summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--LedgerSMB/AM.pm8
-rw-r--r--LedgerSMB/DBObject/Payment.pm21
-rwxr-xr-xLedgerSMB/Template.pm23
-rwxr-xr-xLedgerSMB/Template/LaTeX.pm6
-rw-r--r--UI/am-account-form.html20
-rw-r--r--UI/payments/payment1.html19
-rw-r--r--UI/payments/payment2.html51
-rw-r--r--bin/am.pl4
-rw-r--r--bin/arapprn.pl8
-rw-r--r--scripts/payment.pl294
-rw-r--r--sql/Pg-database.sql35
-rw-r--r--sql/modules/Payment.sql345
12 files changed, 604 insertions, 230 deletions
diff --git a/LedgerSMB/AM.pm b/LedgerSMB/AM.pm
index e1ff4043..d502bc20 100644
--- a/LedgerSMB/AM.pm
+++ b/LedgerSMB/AM.pm
@@ -159,10 +159,10 @@ sub save_account {
$form->{link} = "";
foreach my $item (
$form->{AR}, $form->{AR_amount}, $form->{AR_tax}, $form->{AR_overpayment},
- $form->{AR_paid}, $form->{AP}, $form->{AP_amount}, $form->{AP_overpayment},
- $form->{AP_tax}, $form->{AP_paid}, $form->{IC},
- $form->{IC_income}, $form->{IC_sale}, $form->{IC_expense},
- $form->{IC_cogs}, $form->{IC_taxpart}, $form->{IC_taxservice}
+ $form->{AR_discount}, $form->{AR_paid}, $form->{AP}, $form->{AP_amount},
+ $form->{AP_overpayment}, $form->{AP_discount}, $form->{AP_tax},
+ $form->{AP_paid}, $form->{IC}, $form->{IC_income}, $form->{IC_sale},
+ $form->{IC_expense}, $form->{IC_cogs}, $form->{IC_taxpart}, $form->{IC_taxservice}
)
{
$form->{link} .= "${item}:" if ($item);
diff --git a/LedgerSMB/DBObject/Payment.pm b/LedgerSMB/DBObject/Payment.pm
index 02f3aad5..6cc23f2e 100644
--- a/LedgerSMB/DBObject/Payment.pm
+++ b/LedgerSMB/DBObject/Payment.pm
@@ -105,6 +105,16 @@ sub get_open_accounts {
return @{$self->{accounts}};
}
+
+sub get_entity_credit_account{
+ my ($self) = @_;
+ @{$self->{entity_accounts}} =
+ $self->exec_method(funcname => 'payment_get_entity_accounts');
+ return @{$self->{entity_accounts}};
+
+}
+
+
=over
=item $payment->get_all_accounts()
@@ -499,4 +509,15 @@ sub check_job {
($self->{job}) = $self->exec_method(funcname => 'job__status');
}
+=item post_payment
+
+This method uses payment_post to store a payment (not a bulk payment) on the database.
+
+=cut
+
+sub post_payment {
+ my ($self) = @_;
+ $self->exec_method(funcname => 'payment_post');
+ $self->{dbh}->commit();
+}
1;
diff --git a/LedgerSMB/Template.pm b/LedgerSMB/Template.pm
index c7386928..e87251fd 100755
--- a/LedgerSMB/Template.pm
+++ b/LedgerSMB/Template.pm
@@ -263,8 +263,8 @@ sub render {
#return $format->can('postprocess')->($self);
my $post = $format->can('postprocess')->($self);
if (!$self->{'noauto'}) {
- $self->output;
# Clean up
+ $self->output;
if ($self->{rendered}) {
unlink($self->{rendered}) or
throw Error::Simple 'Unable to delete output file';
@@ -276,15 +276,17 @@ sub render {
sub output {
my $self = shift;
my %args = @_;
+ $self->{output_args} = \%args;
my $method = $self->{method} || $args{method} || $args{media};
-
if ('email' eq lc $method) {
$self->_email_output;
} elsif ('print' eq lc $method) {
$self->_lpr_output;
- } elsif (defined $self->{output}) {
+ } elsif (defined $self->{output} or $method = 'Screen') {
$self->_http_output;
exit;
+ } elsif (defined $method) {
+ $self->_lpr_output;
} else {
$self->_http_output_file;
}
@@ -376,15 +378,22 @@ sub _email_output {
}
sub _lpr_output {
- my ($self) = shift;
+ my ($self, $in_args) = shift;
my $args = $self->{output_args};
- if ($self->{format} != /(pdf|ps)/){
+ if ($self->{format} ne 'LaTeX') {
throw Error::Simple "Invalid Format";
}
- my $lpr = $LedgerSMB::Sysconfig::printer{$args->{printer}};
+ my $lpr = $LedgerSMB::Sysconfig::printer{$args->{media}};
open(LPR, '|-', $lpr);
- print LPR $self->{output};
+
+ # Output is not defined here. In the future we should consider
+ # changing this to use the system command and hit the file as an arg.
+ # -- CT
+ open (FILE, '<', "$self->{rendered}");
+ while (my $line = <FILE>){
+ print LPR $line;
+ }
close(LPR);
}
diff --git a/LedgerSMB/Template/LaTeX.pm b/LedgerSMB/Template/LaTeX.pm
index 973cb724..ea04bb80 100755
--- a/LedgerSMB/Template/LaTeX.pm
+++ b/LedgerSMB/Template/LaTeX.pm
@@ -77,8 +77,10 @@ sub preprocess {
$vars = $rawvars;
}
#XXX Fix escaping
- $vars =~ s/([&\$\\_<>~^#\%\{\}])/\\$1/g;
- $vars =~ s/"(.*)"/``$1''/gs;
+ if (defined $vars){
+ $vars =~ s/([&\$\\_<>~^#\%\{\}])/\\$1/g;
+ $vars =~ s/"(.*)"/``$1''/gs;
+ }
} else {
for ( keys %{$rawvars} ) {
$vars->{$_} = preprocess($rawvars->{$_});
diff --git a/UI/am-account-form.html b/UI/am-account-form.html
index 64bffb8b..ec37f9b2 100644
--- a/UI/am-account-form.html
+++ b/UI/am-account-form.html
@@ -153,7 +153,13 @@
type => 'checkbox',
label => text('Overpayment'),
${form.AR_overpayment} => form.AR_overpayment,
- value => 'AR_overpayment'} ?>
+ value => 'AR_overpayment'} ?><br />
+ <?lsmb INCLUDE input element_data={
+ name => 'AR_discount',
+ type => 'checkbox',
+ label => text('Discount'),
+ ${form.AR_discount} => form.AR_discount,
+ value => 'AR_discount'} ?>
</td>
<td align="right">
@@ -180,9 +186,15 @@
type => 'checkbox',
label => text('Overpayment'),
${form.AP_overpayment} => form.AP_overpayment,
- value => 'AP_overpayment'} ?>
+ value => 'AP_overpayment'} ?><br />
+ <?lsmb INCLUDE input element_data={
+ name => 'AP_discount',
+ type => 'checkbox',
+ label => text('Discount'),
+ ${form.AP_overpayment} => form.AP_discount,
+ value => 'AP_discount'} ?>
</td>
- <td align="right">
+ <td align="right" valign="top">
<?lsmb INCLUDE input element_data={
name => 'IC_sale',
type => 'checkbox',
@@ -202,7 +214,7 @@
${form.IC_taxpart} => form.IC_taxpart,
value => 'IC_taxpart'} ?>
</td>
- <td align="right">
+ <td align="right" valign="top">
<?lsmb INCLUDE input element_data={
name => 'IC_income',
type => 'checkbox',
diff --git a/UI/payments/payment1.html b/UI/payments/payment1.html
index 6d2d2216..1df90592 100644
--- a/UI/payments/payment1.html
+++ b/UI/payments/payment1.html
@@ -37,15 +37,22 @@
</tr>
<tr id="vc-currency-row">
<td id="vc-label-column" class="label-left">
- <?lsmb IF (type.value == 'receipt') ?>
- <?lsmb text('Customer')?>
- <?lsmb ELSE; text('Vendor')?>
- <?lsmb END ?>
-
+ <?lsmb IF (type.value == 'receipt') -?>
+ <?lsmb text('Customer Name') -?>
+ <?lsmb ELSE; text('Vendor Name') -?>
+ <?lsmb END -?>
</td>
<td id="vc-column">
- <?lsmb PROCESS select element_data=vendor_customer ?>
+ <input id="vc_name" name="vc_name"/>
</td>
+ <td id="vc-label-column2" align="right">
+ <?lsmb text('SSN')?>
+ </td>
+ <td id="vc-column2">
+ <input id="vc_ssn" name="vc_ssn" size="10"/>
+ </td>
+ </tr>
+ <tr>
<td id="currency-label-column" class="label-left"><?lsmb text('Currency') ?></td>
<td id="currency-column">
<?lsmb PROCESS select element_data=curr ?>
diff --git a/UI/payments/payment2.html b/UI/payments/payment2.html
index 335fc778..e2824349 100644
--- a/UI/payments/payment2.html
+++ b/UI/payments/payment2.html
@@ -19,10 +19,16 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif',
<?lsmb accountclass.type = 'hidden';
INCLUDE input element_data=accountclass ?>
<?lsmb login.type = 'hidden' ; INCLUDE input element_data=login ?>
-
+ <?lsmb #WE NEED TO KNOW HOW MANY COLUMNS ARE WE USING, PLEASE DO NOT MOVE THE NEXT LINE -?>
+ <?lsmb column_count = 0 -?>
+
<table width="100%">
<tr id="top_bar" class="listtop">
- <th id="top_bar_header" class="listtop"><?lsmb header.text ?></th>
+ <th id="top_bar_header" class="listtop">
+ <?lsmb header.text ?>
+ <?lsmb type.type = 'hidden' ; INCLUDE input element_data=type ?>
+ </th>
+
</tr>
</table>
<table width="100%" id="info_table">
@@ -136,6 +142,7 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif',
<table width="100%">
<tr class="listheading">
<?lsmb FOREACH column IN column_headers # Loop through columns ?>
+ <?lsmb column_count = column_count + 1 -?>
<th class="listheading"><?lsmb column.text ?></th>
<?lsmb END ?>
</tr>
@@ -150,6 +157,7 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif',
<td><?lsmb row.invoice_date ?></td>
<td><?lsmb row.amount ?></td>
<td><?lsmb row.paid ?></td>
+ <td><?lsmb row.discount ?></td>
<td><?lsmb row.due ?></td>
<?lsmb IF defaultcurrency.text != curr.value ?>
<td><?lsmb row.exchange_rate ?></td>
@@ -157,7 +165,7 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif',
<td><div id="<?lsmb "div_topay_invoice_$i" ?>"><?lsmb row.topay ?></div></td>
<?lsmb END ?>
<?lsmb #This should be computed and updated to the div using ?>
- <td><?lsmb INCLUDE input element_data=row.topay_fx;
+ <td><?lsmb row.topay_fx.id = row.topay_fx.name ;INCLUDE input element_data=row.topay_fx;
topay_subtotal = topay_subtotal + row.topay_fx.value -?>
<?lsmb # A DEGRADABLE JAVASCRIPT IS USED, BECAUSE OF THE VISUAL IMPACT
# THAT CAUSES THE SIZE OF THIS DIV -?>
@@ -202,11 +210,7 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif',
<td valign="MIDDLE"><input type="checkbox" name="checkbox_<?lsmb row.invoice.id ?>"></td>
<?lsmb END ?>
</tr>
- <?lsmb IF exrate.text == 1 -?>
- <?lsmb colspan = 5 # We will use this later on totals -?>
- <?lsmb ELSE -?>
- <?lsmb colspan = 8 # We will use this later on totals -?>
- <?lsmb END -?>
+ <?lsmb colspan = column_count - 2 # We will use this later on totals -?>
<tr class="listsubtotal">
<th colspan="<?lsmb colspan -?>" align="right"><?lsmb text('Subtotal') -?></th>
<th colspan="2"><?lsmb topay_subtotal -?>&nbsp;<?lsmb curr.value -?></th>
@@ -214,12 +218,14 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif',
</table>
<table width="100%">
<tr>
- <th class="listheading" colspan="5" ><?lsmb text('OVERPAYMENT / ADVANCED PAYMENT / PREPAYMENT') ?></th>
+ <th class="listheading" colspan="7" ><?lsmb text('OVERPAYMENT / ADVANCED PAYMENT / PREPAYMENT') ?></th>
</tr>
<tr>
<th class="listheading"><?lsmb text('Item') ?></th>
<th class="listheading"><?lsmb text('Account') ?></th>
+ <th class="listheading"><?lsmb text('Cash Account')?></th>
<th class="listheading"><?lsmb text('Source') ?></th>
+ <th class="listheading"><?lsmb text('Memo') ?></th>
<th class="listheading"><?lsmb text('Amount') ?></th>
<th class="listheading">X</th>
</tr>
@@ -235,11 +241,22 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif',
<input type="hidden" id="overpayment_account_<?lsmb overpayment_item -?>" name="overpayment_account_<?lsmb overpayment_item -?>" value="<?lsmb item.account.id -?>--<?lsmb item.account.accno ?>--<?lsmb item.account.description ?>" />
</td>
<td align="center">
+ <?lsmb item.cashaccount.accno -?>--<?lsmb item.cashaccount.description -?>
+ <input type="hidden" id="overpayment_cash_account_<?lsmb overpayment_item -?>"
+ name="overpayment_cash_account_<?lsmb overpayment_item -?>"
+ value="<?lsmb item.cashaccount.id -?>--<?lsmb item.cashaccount.accno ?>--<?lsmb
+ item.cashaccount.description ?>" />
+ </td>
+ <td align="center">
<?lsmb item.source1 -?> <?lsmb item.source2 -?>
<input type="hidden" id="overpayment_source1_<?lsmb overpayment_item -?>" name="overpayment_source1_<?lsmb overpayment_item -?>" value="<?lsmb item.source1 -?>" />
<input type="hidden" id="overpayment_source2_<?lsmb overpayment_item -?>" name="overpayment_source2_<?lsmb overpayment_item -?>" value="<?lsmb item.source2 -?>" />
</td>
<td align="center">
+ <?lsmb item.memo -?>
+ <input type="hidden" id="overpayment_memo_<?lsmb overpayment_item -?>" name="overpayment_memo_<?lsmb overpayment_item -?>" value="<?lsmb item.memo -?>" />
+ </td>
+ <td align="center">
<?lsmb item.amount -?>
<input type="hidden" id="overpayment_topay_<?lsmb overpayment_item -?>" name="overpayment_topay_<?lsmb overpayment_item -?>" value="<?lsmb item.amount ?>" />
<?lsmb overpayment_subtotal = overpayment_subtotal + item.amount -?>
@@ -260,6 +277,13 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif',
<?lsmb END -?>
</select>
</td>
+ <td align="center">
+ <select id="overpayment_cash_account_<?lsmb overpayment_item -?>" name="overpayment_cash_account_<?lsmb overpayment_item -?>">
+ <?lsmb FOREACH item IN account -?>
+ <option value="<?lsmb item.id -?>--<?lsmb item.accno ?>--<?lsmb item.description ?>"><?lsmb item.description -?></option>
+ <?lsmb END -?>
+ </select>
+ </td>
<td align="center">
<select id="overpayment_source1_<?lsmb overpayment_item -?>" name="overpayment_source1_<?lsmb overpayment_item -?>">
<?lsmb FOREACH item IN source -?>
@@ -269,16 +293,17 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif',
<input name="overpayment_source2_<?lsmb overpayment_item ?>" id="overpayment_source2_<?lsmb overpayment_item ?>" />
<input type="hidden" name="overpayment_qty" id="overpayment_qty" value="<?lsmb overpayment_item ?>" />
</td>
+ <td align="center"><input name="overpayment_memo_<?lsmb overpayment_item -?>" id="overpayment_memo_<?lsmboverpayment_item ?>" /></td>
<td align="center"><input name="overpayment_topay_<?lsmb overpayment_item -?>" id="overpayment_topay_<?lsmboverpayment_item ?>" /></td>
<td align="center"><input type="checkbox" name="overpayment_checkbox_<?lsmb overpayment_item -?>"/></td>
</tr>
<tr class="listsubtotal">
- <th colspan="3" align="right"><?lsmb text('Subtotal') -?></th>
+ <th colspan="5" align="right"><?lsmb text('Subtotal') -?></th>
<th colspan="2"><?lsmb overpayment_subtotal -?>&nbsp;<?lsmb curr.value -?></th>
</tr>
<tr class="listtotal">
<?lsmb payment_total = overpayment_subtotal + topay_subtotal -?>
- <th colspan="3" align="right"><?lsmb text('Total') -?></th>
+ <th colspan="5" align="right"><?lsmb text('Total') -?></th>
<th colspan="2"><?lsmb payment_total -?>&nbsp;<?lsmb curr.value -?></th>
</tr>
</table>
@@ -293,13 +318,13 @@ onLoad="maximize_minimize_on_load('div_topay_state', 'UI/payments/img/down.gif',
<?lsmb post.accesskey = "P";
post.title = "POST ALT+P";
post.name = "action";
- post.value = "post";
+ post.value = "post_payment";
post.text = text("POST");
INCLUDE button element_data=post -?>
<?lsmb post_and_print.accesskey = "R";
post_and_print.title = "POST AND PRINT ALT+R";
post_and_print.name = "action";
- post_and_print.value = "post_and_print";
+ post_and_print.value = "post_and_print_payment";
post_and_print.text = text("POST AND PRINT");;
INCLUDE button element_data=post_and_print ?>
<?lsmb INCLUDE select element_data=format ?>
diff --git a/bin/am.pl b/bin/am.pl
index b30b4fe2..f92e380e 100644
--- a/bin/am.pl
+++ b/bin/am.pl
@@ -195,7 +195,7 @@ sub save_account {
) if length $a > 2;
for (
- qw(AR_amount AR_tax AR_paid AR_overpayment AP_amount AP_tax AP_paid AP_overpayment IC_taxpart IC_taxservice IC_sale IC_cogs IC_income IC_expense)
+ qw(AR_amount AR_tax AR_paid AR_overpayment AR_discount AP_amount AP_tax AP_paid AP_overpayment AP_discount IC_taxpart IC_taxservice IC_sale IC_cogs IC_income IC_expense)
)
{
$form->error(
@@ -208,7 +208,7 @@ sub save_account {
foreach $item ( "AR", "AP" ) {
$i = 0;
- for ( "${item}_amount", "${item}_paid", "${item}_tax", "${item}_overpayment" ) {
+ for ( "${item}_amount", "${item}_paid", "${item}_tax", "${item}_overpayment", "${item}_discount" ) {
$i++ if $form->{$_};
}
$form->error(
diff --git a/bin/arapprn.pl b/bin/arapprn.pl
index 3b733004..2cc37f46 100644
--- a/bin/arapprn.pl
+++ b/bin/arapprn.pl
@@ -289,10 +289,13 @@ sub print_check {
my $template = LedgerSMB::Template->new(
user => \%myconfig, template => $form->{'formname'},
- format => uc $form->{'format'} );
+ format => uc $form->{'format'},
+ no_auto_output => 1,
+ output_args => $form,
+ );
try {
$template->render($form);
- $template->output(%{$form});
+ $template->output($form);
}
catch Error::Simple with {
my $E = shift;
@@ -557,6 +560,7 @@ sub print_transaction {
my $template = LedgerSMB::Template->new(
user => \%myconfig, template => $form->{'formname'},
+ no_auto_output => 1,
format => uc $form->{format} );
try {
$template->render($form);
diff --git a/scripts/payment.pl b/scripts/payment.pl
index d50eb15b..ed83b9c8 100644
--- a/scripts/payment.pl
+++ b/scripts/payment.pl
@@ -274,7 +274,7 @@ sub display_payments {
=item payment
This method is used to set the filter screen and prints it, using the
-TT2 system. (hopefully it will... )
+TT2 system.
=back
@@ -284,7 +284,6 @@ sub payment {
my ($request) = @_;
my $locale = $request->{_locale};
my $dbPayment = LedgerSMB::DBObject::Payment->new({'base' => $request});
-
# Lets get the project data...
my @projectOptions;
my @arrayOptions = $dbPayment->list_open_projects();
@@ -303,15 +302,7 @@ sub payment {
push @departmentOptions, { value => $arrayOptions[$ref]->{id}."--".$arrayOptions[$ref]->{description},
text => $arrayOptions[$ref]->{description}};
}
-
-# Lets get the customer or vendor :)
- my @vcOptions;
- @arrayOptions = $dbPayment->get_open_accounts();
- for my $ref (0 .. $#arrayOptions) {
- push @vcOptions, { value => $arrayOptions[$ref]->{id}.'--'.$arrayOptions[$ref]->{name},
- text => $arrayOptions[$ref]->{name}};
- }
-# Lets get the open currencies (this uses the $dbPayment->{account_class} property)
+# Lets get the currencies (this uses the $dbPayment->{account_class} property)
my @currOptions;
@arrayOptions = $dbPayment->get_open_currencies();
for my $ref (0 .. $#arrayOptions) {
@@ -321,7 +312,6 @@ sub payment {
# Lets build filter by period
my $date = LedgerSMB::DBObject::Date->new({base => $request});
$date->build_filter_by_period($locale);
-
# Lets set the data in a hash for the template system. :)
my $select = {
stylesheet => $request->{_user}->{stylesheet},
@@ -335,10 +325,6 @@ my $select = {
name => 'department',
options => \@departmentOptions
},
- vendor_customer => {
- name => 'vendor-customer',
- options => \@vcOptions
- },
curr => {
name => 'curr',
options => \@currOptions
@@ -368,20 +354,89 @@ my $select = {
},
action => {
name => 'action',
- value => 'payment2',
+ value => 'payment1_5',
text => $locale->text("Continue"),
- },
+ }
};
-# Lets call upon the template system
-my $template;
- $template = LedgerSMB::Template->new(
- user => $request->{_user},
- locale => $request->{_locale},
- path => 'UI/payments',
- template => 'payment1',
- format => 'HTML', );
-$template->render($select);# And finally, Lets print the screen :)
+ my $template;
+ $template = LedgerSMB::Template->new(
+ user => $request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/payments',
+ template => 'payment1',
+ format => 'HTML' );
+ $template->render($select);# And finally, Lets print the screen :)
+}
+
+
+=pod
+
+=item payment1_5
+
+This method is called between payment and payment2, it will search the database
+for entity_credit_accounts that match the parameter, if only one is found it will
+run unnoticed by the user, if more than one is found it will ask the user to pick
+one to handle the payment against
+
+=back
+
+=cut
+
+sub payment1_5 {
+my ($request) = @_;
+my $locale = $request->{_locale};
+my $dbPayment = LedgerSMB::DBObject::Payment->new({'base' => $request});
+my @array_options = $dbPayment->get_entity_credit_account();
+ if ($#array_options == -1) {
+ &payment($request);
+ } elsif ($#array_options == 0) {
+ $request->{'vendor-customer'} = $array_options[0]->{id}.'--'.$array_options[0]->{name};
+ &payment2($request);
+ } else {
+ # Lets call upon the template system
+
+ my @company_options;
+ for my $ref (0 .. $#array_options) {
+ push @company_options, { id => $array_options[$ref]->{id},
+ name => $array_options[$ref]->{name}};
+ }
+ my $select = {
+ companies => \@company_options,
+ stylesheet => $request->{_user}->{stylesheet},
+ login => { name => 'login',
+ value => $request->{_user}->{login}},
+ department => { name => 'department',
+ value => $request->{department}},
+ currency => { name => 'curr',
+ value => $request->{curr}},
+ datefrom => { name => 'datefrom',
+ value => $request->{datefrom}},
+ dateto => { name => 'dateto',
+ value => $request->{dateto}},
+ amountfrom => { name => 'amountfrom',
+ value => $request->{datefrom}},
+ amountto => { name => 'amountto',
+ value => $request->{dateto}},
+ accountclass => { name => 'account_class',
+ value => $dbPayment->{account_class}},
+ type => { name => 'type',
+ value => $request->{type}},
+ action => { name => 'action',
+ value => 'payment2',
+ text => $locale->text("Continue")}
+ };
+ my $template;
+ $template = LedgerSMB::Template->new(
+ user => $request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/payments',
+ template => 'payment1_5',
+ format => 'HTML' );
+ eval {$template->render($select) };
+ if ($@) { $request->error("$@"); } # PRINT ERRORS ON THE UI
+ }
+
}
=pod
@@ -409,14 +464,12 @@ my @array_options;
my @currency_options;
my $exchangerate;
# LETS GET THE CUSTOMER/VENDOR INFORMATION
-# TODO TODO TODO TODO TODO TODO TODO
-($Payment->{entity_id}, $Payment->{company_name}) = split /--/ , $request->{'vendor-customer'};
+($Payment->{entity_credit_id}, $Payment->{company_name}) = split /--/ , $request->{'vendor-customer'};
+
# WE NEED TO RETRIEVE A BILLING LOCATION, THIS IS HARDCODED FOR NOW... Should we change it?
$Payment->{location_class_id} = '1';
-#$request->error($Payment->{entity_id});
my @vc_options;
@vc_options = $Payment->get_vc_info();
-# TODO TODO TODO TODO TODO TODO TODO
# LETS BUILD THE PROJECTS INFO
# I DONT KNOW IF I NEED ALL THIS, BUT AS IT IS AVAILABLE I'LL STORE IT FOR LATER USAGE.
if ($request->{projects}) {
@@ -450,6 +503,7 @@ my @column_headers = ({text => $locale->text('Invoice')},
{text => $locale->text('Date')},
{text => $locale->text('Total').$default_currency_text},
{text => $locale->text('Paid').$default_currency_text},
+ {text => $locale->text('Discount').$default_currency_text},
{text => $locale->text('Amount Due').$default_currency_text},
{text => $locale->text('To pay').$default_currency_text}
);
@@ -461,7 +515,7 @@ my @column_headers = ({text => $locale->text('Invoice')},
{text => $locale->text('Amount Due').$currency_text},
{text => $locale->text('To pay').$currency_text};
# WE SET THEM IN THE RIGHT ORDER FOR THE TABLE INSIDE THE UI
- @column_headers[5,6,7] = @column_headers[6,7,5];
+ @column_headers[6,7,8] = @column_headers[7,8,6];
# DOES THE CURRENCY IN USE HAS AN EXCHANGE RATE?, IF SO
# WE MUST SET THE VALUE, OTHERWISE THE UI WILL HANDLE IT
$exchangerate = $request->{exrate} ?
@@ -500,10 +554,12 @@ my @topay_state; # WE WILL USE THIS TO HELP UI TO DETERMINE WHAT IS VISIBLE
for my $ref (0 .. $#array_options) {
if ( !$request->{"checkbox_$array_options[$ref]->{invoice_id}"}) {
+# SHOULD I APPLY DISCCOUNTS?
+
# LETS SET THE EXCHANGERATE VALUES
my $due_fx; my $topay_fx_value;
if ("$exchangerate") {
- $topay_fx_value = $due_fx = "$array_options[$ref]->{due}"/"$exchangerate";
+ $topay_fx_value = $due_fx = "$array_options[$ref]->{due}"/"$exchangerate" - "$array_options[$ref]->{discount}"/"$exchangerate";
} else {
$topay_fx_value = $due_fx = "N/A";
}
@@ -513,11 +569,12 @@ for my $ref (0 .. $#array_options) {
},
invoice_date => "$array_options[$ref]->{invoice_date}",
amount => "$array_options[$ref]->{amount}",
- due => "$array_options[$ref]->{due}",
+ due => "$array_options[$ref]->{due}" - "$array_options[$ref]->{discount}",
paid => "$array_options[$ref]->{amount}" - "$array_options[$ref]->{due}",
+ discount => "$array_options[$ref]->{discount}",
exchange_rate => "$exchangerate",
due_fx => $due_fx, # This was set at the begining of the for statement
- topay => "$array_options[$ref]->{due}",
+ topay => "$array_options[$ref]->{due}" - "$array_options[$ref]->{discount}",
source_text => $request->{"source_text_$array_options[$ref]->{invoice_id}"},
optional => $request->{"optional_pay_$array_options[$ref]->{invoice_id}"},
selected_account => $request->{"account_$array_options[$ref]->{invoice_id}"},
@@ -555,24 +612,46 @@ for (my $i=1 ; $i <= $request->{overpayment_qty}; $i++) {
if ( $request->{"overpayment_topay_$i"} ) {
# Now we split the account selected options
my ($id, $accno, $description) = split(/--/, $request->{"overpayment_account_$i"});
+ my ($cashid, $cashaccno, $cashdescription ) = split(/--/, $request->{"overpayment_cash_account_$i"});
push @overpayment, {amount => $request->{"overpayment_topay_$i"},
source1 => $request->{"overpayment_source1_$i"},
source2 => $request->{"overpayment_source2_$i"},
+ memo => $request->{"overpayment_memo_$i"},
account => { id => $id,
accno => $accno,
description => $description
- }
+ },
+ cashaccount => { id => $cashid,
+ accno => $cashaccno,
+ description => $cashdescription
+ }
};
} else {
$i = $request->{overpayment_qty} + 1;
}
}
+}
+# We need to set the availible media and format from printing
+my @media_options;
+push @media_options, {value => 1, text => "Screen"};
+if ($#{LedgerSMB::Sysconfig::printer}) {
+ for (keys %{LedgerSMB::Sysconfig::printer}) {
+ push @media_options, {value => 1, text => $_};
+ }
}
+#$request->error("@media_options");
+my @format_options;
+push @format_options, {value => 1, text => "HTML"};
+if (${LedgerSMB::Sysconfig::latex}) {
+ push @format_options, {value => 2, text => "PDF" }, {value => 3, text => "POSTSCRIPT" };
+}
# LETS BUILD THE SELECTION FOR THE UI
my $select = {
stylesheet => $request->{_user}->{stylesheet},
header => { text => $request->{type} eq 'receipt' ? $locale->text('Receipt') : $locale->text('Payment') },
+ type => { name => 'type',
+ value => $request->{type} },
login => { name => 'login',
value => $request->{_user}->{login} },
accountclass => {
@@ -615,19 +694,11 @@ my $select = {
},
format => {
name => 'FORMAT',
- options => [
- {value => 1, text => "HTML" },
- {value => 2, text => "PDF" },
- {value => 3, text => "POSTSCRIPT" }
- ],
- },
- media => {
+ options => \@format_options
+ },
+ media => {
name => 'MEDIA',
- options => [
- {value => 1, text => "Screen" },
- {value => 2, text => "PRINTER" },
- {value => 3, text => "EMAIL" }
- ],
+ options => \@media_options
},
exrate => @currency_options,
selectedcheckboxes => @selected_checkboxes ? \@selected_checkboxes : '',
@@ -646,5 +717,132 @@ eval {$template->render($select) };
}
+=pod
+
+=item post_payment
+
+This method is used for the payment module (not the bulk payment),
+and its used for all the mechanics of storing a payment.
+
+=back
+
+=cut
+
+sub post_payment {
+my ($request) = @_;
+my $locale = $request->{_locale};
+my $Payment = LedgerSMB::DBObject::Payment->new({'base' => $request});
+# LETS GET THE CUSTOMER/VENDOR INFORMATION
+($Payment->{entity_id}, $Payment->{company_name}) = split /--/ , $request->{'vendor-customer'};
+# LETS GET THE DEPARTMENT INFO
+# WE HAVE TO SET $dbPayment->{department_id} in order to process
+if ($request->{department}) {
+ $request->{department} =~ /^(\d+)--*/;
+ $Payment->{department_id} = $1;
+}
+#
+# We want to set a gl_description,
+# since we are using two tables there is no need to use doubled information,
+# we could specify this gl is the result of a payment movement...
+#
+$Payment->{gl_description} = $locale->text('This gl movement, is the result of a payment transaction');
+#
+# Im not sure what this is for... gotta comment this later
+$Payment->{approved} = 'true';
+#
+# We have to setup a lot of things before we can process the payment
+# they are related to payment_post sql function, so if you have any doubts
+# look there.
+#-------------------------------------------------------------------------
+#
+# Variable definition
+#
+# We use the prefix op to refer to the overpayment variables.
+my $overpayment; # This variable might be fuzzy, we are using it to handle invalid data
+ # i.e. a user set an overpayment qty inside an invoice.
+my @array_options;
+my @amount;
+my @cash_account_id;
+my @source;
+my @transaction_id;
+my @op_amount;
+my @op_cash_account_id;
+my @op_source;
+my @op_memo;
+my @op_account_id;
+#
+# We need the invoices in order to process the income data, this is done this way
+# since the data we have isn't indexed in any way.
+#
+
+@array_options = $Payment->get_open_invoices();
+for my $ref (0 .. $#array_options) {
+ if ( !$request->{"checkbox_$array_options[$ref]->{invoice_id}"}) {
+ #
+ # The prefix cash is to set the movements of the cash accounts,
+ # same names are used for ap/ar accounts w/o the cash prefix.
+ #
+ # Maybe i should move this to another sub, so i can call it from payment2 as well :). D.M.
+ if ($array_options[$ref]->{amount} < $request->{"topay_$array_options[$ref]->{invoice_id}"} ) {
+ # THERE IS AN OVERPAYMENT!, we should store it and see if we can use it on the UI
+ $overpayment = $overpayment + $request->{"topay_$array_options[$ref]->{invoice_id}"} - $array_options[$ref]->{amount};
+ $request->{"topay_$array_options[$ref]->{invoice_id}"} = $request->{"topay_$array_options[$ref]->{invoice_id}"};
+ }
+ push @amount, $request->{"topay_fx_$array_options[$ref]->{invoice_id}"}; # We'll use this for both cash and ap/ar accounts
+ push @cash_account_id, $request->{"optional_pay_$array_options[$ref]->{invoice_id}"} ? $request->{"account_$array_options[$ref]->{invoice_id}"} : $request->{account};
+ push @source, $request->{"source1_$array_options[$ref]->{invoice_id}"}.' '.$request->{"source2_$array_options[$ref]->{invoice_id}"}; # We'll use this for both source and ap/ar accounts
+ push @transaction_id, $array_options[$ref]->{invoice_id};
+ }
+}
+#
+# Now we need the overpayment information.
+#
+# We will use the prefix op to indicate it is an overpayment information.
+#
+# note: I love the for's C-like syntax.
+
+for (my $i=1 ; $i <= $request->{overpayment_qty}; $i++) {
+ if (!$request->{"overpayment_checkbox_$i"}) { # Is overpayment marked as deleted ?
+ if ( $request->{"overpayment_topay_$i"} ) { # Is this overpayment an used field?
+ # Now we split the account selected options, using the namespace the if statement
+ # provides for us.
+ $request->{"overpayment_account_$i"} =~ /^(\d+)--*/;
+ my $id = $1;
+ $request->{"overpayment_cash_account_$i"} =~ /^(\d+)--*/;
+ my $cashid = $1;
+ push @op_amount, $request->{"overpayment_topay_$i"};
+ push @op_cash_account_id, $cashid;
+ push @op_source, $request->{"overpayment_source1_$i"}.' '.$request->{"overpayment_source2_$i"};
+ push @op_memo, $request->{"overpayment_memo_$i"};
+ push @op_account_id, $id;
+ }
+ }
+}
+
+# Finally we store all the data inside the LedgerSMB::DBObject::Payment object.
+ $Payment->{cash_account_id} = $Payment->_db_array_scalars(@cash_account_id);
+ $Payment->{amount} = $Payment->_db_array_scalars(@amount);
+ $Payment->{source} = $Payment->_db_array_scalars(@source);
+ $Payment->{transaction_id} = $Payment->_db_array_scalars(@transaction_id);
+ $Payment->{op_amount} = $Payment->_db_array_scalars(@op_amount);
+ $Payment->{op_cash_account_id} = $Payment->_db_array_scalars(@op_cash_account_id);
+ $Payment->{op_source} = $Payment->_db_array_scalars(@op_source);
+ $Payment->{op_memo} = $Payment->_db_array_scalars(@op_memo);
+ $Payment->{op_account_id} = $Payment->_db_array_scalars(@op_account_id);
+# Ok, hoping for the best...
+ $Payment->post_payment();
+# We've gotta print anything, in the near future this will redirect to a new payment.
+ my $select = {};
+ my $template = LedgerSMB::Template->new(
+ user => $request->{_user},
+ locale => $request->{_locale},
+ path => 'UI/payments',
+ template => 'payment2',
+ format => 'HTML' );
+ eval {$template->render($select) };
+ if ($@) { $request->error("$@"); } # PRINT ERRORS ON THE UI
+
+}
+
eval { do "scripts/custom/payment.pl"};
1;
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index ca907d50..ef6c4de2 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -359,7 +359,12 @@ glnumber|1
projectnumber|1
queue_payments|0
poll_frequency|1
+<<<<<<< .mine
+rcptnumber|1
+paynumber|1
+=======
separate_duties|1
+>>>>>>> .r2141
\.
COMMENT ON TABLE defaults IS $$
@@ -469,6 +474,7 @@ CREATE TABLE entity_credit_account (
entity_class int not null references entity_class(id) check ( entity_class in (1,2) ),
discount numeric,
discount_terms int default 0,
+ discount_account_id int references chart(id),
taxincluded bool default 'f',
creditlimit NUMERIC default 0,
terms int2 default 0,
@@ -738,35 +744,6 @@ CREATE TABLE ap (
);
COMMENT ON COLUMN ap.entity_id IS $$ Used to be customer_id, but customer is now metadata. You need to push to entity $$;
-
--- Payment stuff by David Mora
-
-CREATE TABLE payment (
- id serial primary key,
- reference text NOT NULL,
- payment_class integer NOT NULL,
- amount numeric NOT NULL,
- payment_date date default current_date,
- closed bool default FALSE,
- person_id integer references person(id),
- currency char(3),
- notes text,
- department_id integer default 0);
-
-COMMENT ON TABLE payment IS $$ This table will store the main data on a payment, prepayment, overpayment, etc... $$;
-COMMENT ON COLUMN payment.reference IS $$ This field will store the code for both receipts and payment orders, it will be differentiate by payment_class $$;
-COMMENT ON COLUMN payment.closed IS $$ This will store the current state of a payment/receipt order $$;
-CREATE INDEX payment_id_idx ON Payment(id);
-
-CREATE TABLE payment_links (
- payment_id integer references Payment(id),
- transaction_id integer NOT NULL,
- amount numeric);
-COMMENT ON TABLE payment_links IS $$ This table will link payment to ar/ap transactions $$;
-COMMENT ON COLUMN payment_links.transaction_id IS $$ This column lacks some data integrity controls that must be implemented in the future $$;
-
-
-
--
CREATE TABLE taxmodule (
taxmodule_id serial PRIMARY KEY,
diff --git a/sql/modules/Payment.sql b/sql/modules/Payment.sql
index 2ab6cf69..87cca5ca 100644
--- a/sql/modules/Payment.sql
+++ b/sql/modules/Payment.sql
@@ -1,3 +1,24 @@
+CREATE OR REPLACE FUNCTION payment_get_entity_accounts
+(in_account_class int,
+ in_vc_name text,
+ in_vc_idn int)
+ returns SETOF entity AS
+ $$
+ DECLARE out_entity entity%ROWTYPE;
+ BEGIN
+ FOR out_entity IN
+ SELECT ec.id, cp.legal_name as name, e.entity_class, e.created
+ FROM entity e
+ JOIN entity_credit_account ec ON (ec.entity_id = e.id)
+ JOIN company cp ON (cp.entity_id = e.id)
+ WHERE ec.entity_class = in_account_class
+ AND (cp.legal_name ilike coalesce('%'||in_vc_name||'%','%%') OR cp.tax_id = in_vc_idn)
+ LOOP
+ RETURN NEXT out_entity;
+ END LOOP;
+ END;
+ $$ LANGUAGE PLPGSQL;
+
-- payment_get_open_accounts and the option to get all accounts need to be
-- refactored and redesigned. -- CT
CREATE OR REPLACE FUNCTION payment_get_open_accounts(in_account_class int)
@@ -11,15 +32,15 @@ BEGIN
JOIN entity_credit_account ec ON (ec.entity_id = e.id)
JOIN company cp ON (cp.entity_id = e.id)
WHERE ec.entity_class = in_account_class
- AND CASE WHEN in_account_class = 1 THEN
- e.id IN (SELECT entity_id FROM ap
- WHERE amount <> paid
- GROUP BY entity_id)
- WHEN in_account_class = 2 THEN
- e.id IN (SELECT entity_id FROM ar
- WHERE amount <> paid
- GROUP BY entity_id)
- END
+ -- AND CASE WHEN in_account_class = 1 THEN
+ -- e.id IN (SELECT entity_id FROM ap
+ -- WHERE amount <> paid
+ -- GROUP BY entity_id)
+ -- WHEN in_account_class = 2 THEN
+ -- e.id IN (SELECT entity_id FROM ar
+ -- WHERE amount <> paid
+ -- GROUP BY entity_id)
+ -- END
LOOP
RETURN NEXT out_entity;
END LOOP;
@@ -74,55 +95,58 @@ RETURNS SETOF payment_invoice AS
$$
DECLARE payment_inv payment_invoice;
BEGIN
- FOR payment_inv IN
- SELECT a.id AS invoice_id, a.invnumber,
- a.transdate AS invoice_date, a.amount,
- CASE WHEN discount_terms
- > extract('days' FROM age(a.transdate))
- THEN 0
- ELSE (a.amount - a.paid) * c.discount / 100
- END AS discount,
- a.amount - a.paid -
- CASE WHEN discount_terms
- > extract('days' FROM age(a.transdate))
- THEN 0
- ELSE (a.amount - a.paid) * c.discount / 100
- END
- AS due
- FROM (SELECT id, invnumber, transdate, amount, entity_id,
+ FOR payment_inv IN
+ SELECT a.id AS invoice_id, a.invnumber AS invnumber,
+ a.transdate AS invoice_date, a.amount AS amount,
+ (CASE WHEN c.discount_terms < extract('days' FROM age(a.transdate))
+ THEN 0
+ ELSE (coalesce(ac.due, a.amount)) * coalesce(c.discount, 0) / 100
+ END) AS discount, ac.due
+ FROM (SELECT id, invnumber, transdate, amount, entity_id,
1 as invoice_class, paid, curr,
entity_credit_account, department_id
FROM ap
UNION
- SELECT id, invnumber, transdate, amount, entity_id,
+ SELECT id, invnumber, transdate, amount, entity_id,
2 AS invoice_class, paid, curr,
entity_credit_account, department_id
- FROM ar
- ) a
- JOIN entity_credit_account c ON (c.id = a.entity_credit_account
- OR (a.entity_credit_account IS NULL and
- a.entity_id = c.entity_id))
- WHERE a.invoice_class = in_account_class
- AND c.entity_class = in_account_class
- AND a.amount - a.paid <> 0
- AND a.curr = in_curr
- AND (a.transdate >= in_datefrom
- OR in_datefrom IS NULL)
- AND (a.transdate <= in_dateto
- OR in_dateto IS NULL)
- AND (a.amount >= in_amountfrom
- OR in_amountfrom IS NULL)
- AND (a.amount <= in_amountto
- OR in_amountto IS NULL)
- AND (a.department_id = in_department_id
- OR in_department_id IS NULL)
-
+ FROM ar
+ ) a
+ JOIN (SELECT trans_id, chart_id, sum(CASE WHEN in_account_class = 1 THEN amount
+ WHEN in_account_class = 2
+ THEN amount * -1
+ END) as due
+ FROM acc_trans
+ GROUP BY trans_id, chart_id) ac ON (ac.trans_id = a.id)
+ JOIN chart ON (chart.id = ac.chart_id)
+ JOIN entity_credit_account c ON (c.id = a.entity_credit_account
+ OR (a.entity_credit_account IS NULL and a.entity_id = c.entity_id))
+ WHERE ((chart.link = 'AP' AND in_account_class = 1)
+ OR (chart.link = 'AR' AND in_account_class = 2))
+ AND a.invoice_class = in_account_class
+ AND c.entity_class = in_account_class
+ AND c.id = in_entity_credit_id
+ AND a.amount - a.paid <> 0
+ AND a.curr = in_curr
+ AND (a.transdate >= in_datefrom
+ OR in_datefrom IS NULL)
+ AND (a.transdate <= in_dateto
+ OR in_dateto IS NULL)
+ AND (a.amount >= in_amountfrom
+ OR in_amountfrom IS NULL)
+ AND (a.amount <= in_amountto
+ OR in_amountto IS NULL)
+ AND (a.department_id = in_department_id
+ OR in_department_id IS NULL)
+ GROUP BY a.invnumber, a.transdate, a.amount, discount, ac.due, a.id, c.discount_terms
LOOP
RETURN NEXT payment_inv;
END LOOP;
END;
$$ LANGUAGE PLPGSQL;
+
+
COMMENT ON FUNCTION payment_get_open_invoices(int, int, char(3), date, date, numeric, numeric, int) IS
$$ This function takes three arguments:
Type: 1 for vendor, 2 for customer
@@ -426,101 +450,201 @@ sub-array, the first element is the (integer) transaction id, and the second
is the amount for that transaction. If the total of the amounts do not add up
to in_total, then an error is generated. $$;
+--
+-- WE NEED A PAYMENT TABLE
+--
+CREATE TABLE payment (
+ id serial primary key,
+ reference text NOT NULL,
+ gl_id integer references gl(id),
+ payment_class integer NOT NULL,
+ payment_date date default current_date,
+ closed bool default FALSE,
+ entity_id integer references entity(id),
+ employee_id integer references entity_employee(entity_id),
+ currency char(3),
+ notes text,
+ department_id integer default 0);
+
+COMMENT ON TABLE payment IS $$ This table will store the main data on a payment, prepayment, overpayment, et$$;
+COMMENT ON COLUMN payment.reference IS $$ This field will store the code for both receipts and payment order $$;
+COMMENT ON COLUMN payment.closed IS $$ This will store the current state of a payment/receipt order $$;
+COMMENT ON COLUMN payment.gl_id IS $$ A payment should always be linked to a GL movement $$;
+CREATE INDEX payment_id_idx ON payment(id);
+
+CREATE TABLE payment_links (
+ payment_id integer references Payment(id),
+ entry_id integer references acc_trans(entry_id),
+ type integer);
+COMMENT ON TABLE payment_links IS $$
+ An explanation to the type field.
+ * A type 0 means the link is referencing an ar/ap and was created
+ using an overpayment movement after the receipt was created
+ * A type 1 means the link is referencing an ar/ap and was made
+ on the payment creation, its not the product of an overpayment movement
+ * A type 2 means the link is not referencing an ar/ap and its the product
+ of the overpayment logic
+
+ With this ideas in order we can do the following
+
+ To get the payment amount we will sum the entries with type > 0.
+ To get the linked amount we will sum the entries with type < 2.
+ The overpayment account can be obtained from the entries with type = 2.
+
+ This reasoning is hacky and i hope it can dissapear when we get to 1.4 - D.M.
+$$;
+
+
CREATE OR REPLACE FUNCTION payment_post
-(in_payment_date date,
- in_account_class int,
- in_person_id int,
- in_currency char(3),
- in_notes text,
- in_department int,
- in_gl_description text,
- in_cash_accno int[],
- in_cash_amount int[],
- in_cash_approved bool[],
- in_cash_source text[],
- in_accno int[],
- in_amount int[],
- in_approved bool[],
- in_source text[],
- in_transaction_id int[],
- in_type int[],
- in_approved bool)
+(in_datepaid date,
+ in_account_class int,
+ in_entity_id int,
+ in_curr char(3),
+ in_notes text,
+ in_department_id int,
+ in_gl_description text,
+ in_cash_account_id int[],
+ in_amount numeric[],
+ in_cash_approved bool[],
+ in_source text[],
+ in_transaction_id int[],
+ in_op_amount numeric[],
+ in_op_cash_account_id int[],
+ in_op_source text[],
+ in_op_memo text[],
+ in_op_account_id int[],
+ in_approved bool)
RETURNS INT AS
$$
DECLARE var_payment_id int;
DECLARE var_gl_id int;
+DECLARE var_entry record;
DECLARE var_entry_id int[];
DECLARE out_count int;
-
+DECLARE coa_id record;
+DECLARE var_employee int;
+DECLARE var_account_id int;
BEGIN
- -- FIRST WE HAVE TO INSERT THE PAYMENT
+ SELECT INTO var_employee entity_id FROM users WHERE username = SESSION_USER LIMIT 1;
+ --
+ -- SECOND WE HAVE TO INSERT THE PAYMENT, USING THE GL INFORMATION
-- THE ID IS GENERATED BY payment_id_seq
--
INSERT INTO payment (reference, payment_class, payment_date,
- person_id, currency, notes, department_id)
+ employee_id, currency, notes, department_id, entity_id)
VALUES ((CASE WHEN in_account_class = 1 THEN
setting_increment('rcptnumber') -- I FOUND THIS ON sql/modules/Settings.sql
ELSE -- and it is very usefull
setting_increment('paynumber')
END),
- in_account_class, in_payment_date, in_person_id,
- in_currency, in_notes, in_department);
+ in_account_class, in_datepaid, var_employee,
+ in_curr, in_notes, in_department_id, in_entity_id);
SELECT currval('payment_id_seq') INTO var_payment_id; -- WE'LL NEED THIS VALUE TO USE payment_link table
- -- SECOND WE HAVE TO MAKE THE GL TO HOLD THE TRANSACTIONS
- -- THE ID IS GENERATED BY gl_id_seq
- --
- INSERT INTO gl (reference, description, transdate,
- person_id, notes, approved, department_id)
- VALUES (setting_increment('glnumber'),
- in_gl_description, in_payment_date, in_person_id,
- in_notes, in_department, coalesce(in_approved, true));
- SELECT currval('id') INTO var_gl_id; -- WE'LL NEED THIS VALUE TO JOIN WITH PAYMENT
+ -- WE'LL NEED THIS VALUE TO JOIN WITH PAYMENT
-- NOW COMES THE HEAVY PART, STORING ALL THE POSSIBLE TRANSACTIONS...
--
-- FIRST WE SHOULD INSERT THE CASH ACCOUNTS
--
-- WE SHOULD HAVE THE DATA STORED AS (ACCNO, AMOUNT), SO
FOR out_count IN
- array_lower(in_cash_accno, 1) ..
- array_upper(in_cash_accno, 1)
+ array_lower(in_cash_account_id, 1) ..
+ array_upper(in_cash_account_id, 1)
LOOP
INSERT INTO acc_trans (chart_id, amount,
trans_id, transdate, approved, source)
- VALUES ((SELECT id FROM chart WHERE accno = in_cash_accno[out_count]),
- CASE WHEN in_account_class = 2 THEN in_cash_amount[out_count] * -1
- ELSE in_cash_amount[out_count]
+ VALUES (in_cash_account_id[out_count],
+ CASE WHEN in_account_class = 2 THEN in_amount[out_count]
+ ELSE in_amount[out_count]* - 1
END,
- var_gl_id, in_payment_date, coalesce(in_cash_approved[1], true),
- in_cash_source[out_count]);
- --SELECT currval('acc_trans_entry_id_seq') INTO var_entry_id[out_count];--WE'LL NEED THIS FOR THE PAYMENT_LINK
+ in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true),
+ in_source[out_count]);
+ INSERT INTO payment_links
+ VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 1);
END LOOP;
- --
- -- NOW LETS HANDLE THE AR/AP/OVERPAYMENT ACCOUNT
- --
- FOR var_count IN
- array_lower(in_accno, 1) ..
- array_upper(in_accno, 1)
+ -- NOW LETS HANDLE THE AR/AP ACCOUNTS
+ -- WE RECEIVED THE TRANSACTIONS_ID AND WE CAN OBTAIN THE ACCOUNT FROM THERE
+ FOR out_count IN
+ array_lower(in_transaction_id, 1) ..
+ array_upper(in_transaction_id, 1)
LOOP
- INSERT INTO acc_trans (chart_id, amount,
- trans_id, transdate, approved, source)
- VALUES ((SELECT id FROM chart WHERE accno = in_accno[out_count]),
+ SELECT INTO var_account_id chart_id FROM acc_trans as ac
+ JOIN chart as c ON (c.id = ac.chart_id)
+ WHERE
+ trans_id = in_transaction_id[out_count] AND
+ ( c.link = 'AP' OR c.link = 'AR' );
+ INSERT INTO acc_trans (chart_id, amount,
+ trans_id, transdate, approved, source)
+ VALUES (var_account_id,
CASE WHEN in_account_class = 2 THEN in_amount[out_count] * -1
ELSE in_amount[out_count]
END,
- var_gl_id, in_payment_date, coalesce(in_approved[1], true),
+ in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true),
in_source[out_count]);
- --
- -- WE WILL INSERT THE LINK INTO PAYMENT_LINKS NOW
- --
- INSERT INTO payment_links
- VALUES (var_payment_id, currval(acc_trans_entry_id_seq),
- in_transaction_id[out_count], in_type[var_count]);
+ INSERT INTO payment_links
+ VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 1);
+
END LOOP;
- return 0;
+--
+-- WE NEED TO HANDLE THE OVERPAYMENTS NOW
+--
+ --
+ -- FIRST WE HAVE TO MAKE THE GL TO HOLD THE OVERPAYMENT TRANSACTIONS
+ -- THE ID IS GENERATED BY gl_id_seq
+ --
+ IF (array_upper(in_op_cash_account_id, 1) > 0) THEN
+ INSERT INTO gl (reference, description, transdate,
+ person_id, notes, approved, department_id)
+ VALUES (setting_increment('glnumber'),
+ in_gl_description, in_datepaid, var_employee,
+ in_notes, in_approved, in_department_id);
+ SELECT currval('id') INTO var_gl_id;
+--
+-- WE NEED TO SET THE GL_ID FIELD ON PAYMENT'S TABLE
+--
+ UPDATE payment SET gl_id = var_gl_id
+ WHERE id = var_payment_id;
+ -- NOW COMES THE HEAVY PART, STORING ALL THE POSSIBLE TRANSACTIONS...
+ --
+ -- FIRST WE SHOULD INSERT THE OVERPAYMENT CASH ACCOUNTS
+ --
+ FOR out_count IN
+ array_lower(in_op_cash_account_id, 1) ..
+ array_upper(in_op_cash_account_id, 1)
+ LOOP
+ INSERT INTO acc_trans (chart_id, amount,
+ trans_id, transdate, approved, source)
+ VALUES (in_op_cash_account_id[out_count],
+ CASE WHEN in_account_class = 2 THEN in_op_amount[out_count]
+ ELSE in_op_amount[out_count] * - 1
+ END,
+ var_gl_id, in_datepaid, coalesce(in_approved, true),
+ in_op_source[out_count]);
+ INSERT INTO payment_links
+ VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 2);
+ END LOOP;
+ -- NOW LETS HANDLE THE OVERPAYMENT ACCOUNTS
+ FOR out_count IN
+ array_lower(in_op_account_id, 1) ..
+ array_upper(in_op_account_id, 1)
+ LOOP
+ INSERT INTO acc_trans (chart_id, amount,
+ trans_id, transdate, approved, source, memo)
+ VALUES (in_op_account_id[out_count],
+ CASE WHEN in_account_class = 2 THEN in_op_amount[out_count] * -1
+ ELSE in_op_amount[out_count]
+ END,
+ var_gl_id, in_datepaid, coalesce(in_approved, true),
+ in_op_source[out_count], in_op_memo[out_count]);
+ INSERT INTO payment_links
+ VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 2);
+ END LOOP;
+ END IF;
+ return 0;
END;
$$ LANGUAGE PLPGSQL;
-- I HAVE TO MAKE A COMMENT ON THIS FUNCTION
+
-- Move this to the projects module when we start on that. CT
CREATE OR REPLACE FUNCTION project_list_open(in_date date)
RETURNS SETOF project AS
@@ -568,15 +692,9 @@ $$
DECLARE resultrow record;
BEGIN
FOR resultrow IN
- SELECT curr AS curr FROM ar
- WHERE amount <> paid
- OR paid IS NULL
- AND in_account_class=2
+ SELECT DISTINCT curr FROM ar
UNION
- SELECT curr FROM ap
- WHERE amount <> paid
- OR paid IS NULL
- AND in_account_class=1
+ SELECT DISTINCT curr FROM ap
ORDER BY curr
LOOP
return next resultrow.curr;
@@ -627,7 +745,7 @@ CREATE TYPE payment_location_result AS (
-- payment_get_vc_info has the same arch as company__list_locations, except for the filtering capabilities
-- This should be unified on the API when we get things working - David Mora
--
-CREATE OR REPLACE FUNCTION payment_get_vc_info(in_entity_id int, in_location_class_id int)
+CREATE OR REPLACE FUNCTION payment_get_vc_info(in_entity_credit_id int, in_location_class_id int)
RETURNS SETOF payment_location_result AS
$$
DECLARE out_row RECORD;
@@ -640,7 +758,8 @@ DECLARE out_row RECORD;
JOIN company cp ON (ctl.company_id = cp.id)
JOIN location_class lc ON (ctl.location_class = lc.id)
JOIN country c ON (c.id = l.country_id)
- WHERE cp.entity_id = in_entity_id AND
+ JOIN entity_credit_account ec ON (ec.entity_id = cp.entity_id)
+ WHERE ec.id = in_entity_credit_id AND
lc.id = in_location_class_id
ORDER BY lc.id, l.id, c.name
LOOP
generated by cgit v1.2.3 (git 2.39.1) at 2024-06-26 04:20:17 +0000