[set page_banner]Journal Entry[/set] [set fm_class]Ledger[/set] [set help_name]journal_entry[/set] [set page_title]Journal Entry[/set] [set icon_name]icon_stats.gif[/set] @_FM_STD_HEAD_@ [seti sqlSentence] BEGIN TRANSACTION; [perl tables="gl_chartofaccount"] my $trans; my $action = $Values->{"mv_action"}; my $status = 200 if $Values->{"approve"} eq 'yes'; my $updFunction = "gl_posting" if $status; $status = 100 if !$status; $updFunction = "gl_posting_prelim" if !$updFunction; my $batchseq = $Values->{"select_batch"} || "currval('gl_batchsequence')"; my $soa = $Values->{"soa"}; my ($totaldr, $totalcr) = (0,0); my $ref = $Scratch->{journals}; my ($ar, $nul, $names) = @$ref; # Calculating totals first for my $journal (@$ar) { my ($x, $journalnr, $jdate, $debit, $credit, $description, $acctinfo) = @$journal; $totaldr += $debit; $totalcr += $credit; }; $Scratch->{remark} = ""; if ($status == 200 && $totaldr != $totalcr && !$soa) { $status = 100; $updFunction = "gl_posting_prelim"; $Scratch->{remark} = "
[L]Batch not balanced. Still not accounted![/L]";
};
if ($action eq "Update") {
$trans .= "DELETE FROM gl_journal WHERE batch_id = $batchseq;\n";
$trans .= "UPDATE gl_batch SET batchnr = [value batchnr], name = '[value batchname]',posteddate= '[value batchdate]', soa=$soa,
totaldr = $totaldr, totalcr = $totalcr, status = $status
WHERE batch_id = $batchseq;\n";
} elsif ($action eq "Delete") {
$trans .= "DELETE FROM gl_batch WHERE batchnr = [value select_batch];";
return;
} else {
$trans .= "INSERT INTO gl_batch (system_id, type, batchnr, name, status, totaldr, totalcr, posteddate, updated)
VALUES (10005, 40001, [value batchnr], '[value batchname]', $status, $totaldr, $totalcr,'[value batchdate]', 'now');\n";
}
for my $journal (@$ar) {
my ($x, $journalnr, $jdate, $debit, $credit, $description, $acctinfo) = @$journal;
my ($acctnr,$pgrp,$cgrp) = split(/\//,$acctinfo);
my ($coa_id, $cgrp_id, $vat_id, $vat_coa);
my $db = $Sql{gl_chartofaccount} or return "NO chart of account?";
my $q = "SELECT coa_id, vat_id, vat_coa FROM gl_chartofaccount WHERE number = '$acctnr'";
my $sth = $db->prepare($q);
$sth->execute or die("Can't execute $q\n");
($coa_id, $vat_id, $vat_coa) = $sth->fetchrow();
# if ($pgrp) {
# $q = "SELECT ac_id FROM pd_productgroup WHERE pgrp_id = $pgrp";
# $sth = $db->prepare($q);
# $sth->execute or die("Can't execute $q\n");
# ($pgrp_id) = $sth->fetchrow();
# };
$pgrp ||= "NULL";
if ($cgrp) {
$q = "SELECT ac_id FROM co_custgrp WHERE custgrp_nr = $cgrp";
$sth = $db->prepare($q);
$sth->execute or die("Can't execute $q\n");
($cgrp_id) = $sth->fetchrow();
};
$cgrp_id ||= "NULL";
# If final accounting of batch, check for vat code and chart of account
if ($status == 200) {
if ($soa && $soa ne "NULL") { # set-off account (switching debit and credit)
$trans .= "SELECT $updFunction($soa, $pgrp, $cgrp_id, '$jdate', $credit, $debit, '$description', 40001, 1, $batchseq, $journalnr);\n";
};
if ($vat_id && $vat_coa) { # Vat information
my ($vat_pct, $vat_debit, $vat_credit);
$q = "SELECT percent FROM pd_vat WHERE vat_id = $vat_id";
$sth = $db->prepare($q);
$sth->execute or die("Can't execute $q\n");
($vat_pct) = $sth->fetchrow() / 100;
$vat_debit = $debit / (1 + $vat_pct) * $vat_pct;
$vat_credit = $credit / (1 + $vat_pct) * $vat_pct;
$debit -= $vat_debit;
$credit -= $vat_credit;
$trans .= "SELECT $updFunction($vat_coa, $pgrp, $cgrp_id, '$jdate', $vat_debit, $vat_credit, '$description', 40001, 1, $batchseq, $journalnr);\n";
};
};
# Update accounting
$trans .= "SELECT $updFunction($coa_id, $pgrp, $cgrp_id, '$jdate', $debit, $credit, '$description', 40001, 1, $batchseq, $journalnr);\n";
};
undef $Values->{select_batch};
$trans;
[/perl]
COMMIT;
[/seti]
[if value mv_action eq Update]
[set returnpage]freemoney/batchList[/set]
[set leadtext]The batch entry has been updated.[/set]
[elsif value mv_action eq Delete]
[set returnpage]freemoney/batchList[/set]
[set leadtext]The batch entry has been deleted:[/set]
[/elsif]
[else]
[set returnpage]freemoney/batchList[/set]
[set leadtext]The batch entry has been added.[/set]
[/else]
[/if]
[value name=mv_action set=""]
[try][sql interpolate=1][scratch sqlSentence][/sql][/try]
[L][scratch leadtext][/L]
[scratch remark]
[catch] [L]There was an error adding the new order. Please check your data.[/L]
[/catch] [L]BACK[/L] @_FM_STD_FOOTER_@