[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[4779] trunk
- Subject: SF.net SVN: ledger-smb:[4779] trunk
- From: ..hidden..
- Date: Fri, 25 May 2012 10:39:00 +0000
Revision: 4779
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4779&view=rev
Author: einhverfr
Date: 2012-05-25 10:38:59 +0000 (Fri, 25 May 2012)
Log Message:
-----------
Invoices with inventory now subject to separation of duties
Modified Paths:
--------------
trunk/LedgerSMB/IR.pm
trunk/LedgerSMB/IS.pm
trunk/UI/Reports/filters/gl.html
trunk/bin/ir.pl
trunk/bin/is.pl
trunk/sql/modules/COGS.sql
trunk/sql/modules/Drafts.sql
Property Changed:
----------------
trunk/
trunk/LedgerSMB/Scripts/account.pm
trunk/LedgerSMB/Scripts/admin.pm
trunk/LedgerSMB/Scripts/customer.pm
trunk/LedgerSMB/Scripts/employee.pm
trunk/LedgerSMB/Scripts/file.pm
trunk/LedgerSMB/Scripts/journal.pm
trunk/LedgerSMB/Scripts/login.pm
trunk/LedgerSMB/Scripts/menu.pm
trunk/LedgerSMB/Scripts/payment.pm
trunk/LedgerSMB/Scripts/recon.pm
trunk/LedgerSMB/Scripts/setup.pm
trunk/LedgerSMB/Scripts/taxform.pm
trunk/LedgerSMB/Scripts/vendor.pm
trunk/sql/upgrade/1.2-1.3-manual.sql
Property changes on: trunk
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3:3711-4756
+ /branches/1.3:3711-4777
Modified: trunk/LedgerSMB/IR.pm
===================================================================
--- trunk/LedgerSMB/IR.pm 2012-05-25 07:58:46 UTC (rev 4778)
+++ trunk/LedgerSMB/IR.pm 2012-05-25 10:38:59 UTC (rev 4779)
@@ -66,6 +66,15 @@
}
+sub add_cogs {
+ my ($self, $form) = @_;
+ my $dbh = $form->{dbh};
+ my $query =
+ "select cogs__add_for_ap_line(id) FROM invoice WHERE trans_id = ?";
+ my $sth = $dbh->prepare($query) || $form->dberror($query);
+ $sth->execute($form->{id}) || $form->dberror($query);
+}
+
sub post_invoice {
my ( $self, $myconfig, $form ) = @_;
@@ -400,29 +409,7 @@
$allocated += $qty;
last if ( ( $totalqty -= $qty ) >= 0 );
}
- } else {
- # start patch bug 1755355 ###############################################################################
- # check for unallocated entries atthe same price to match our entry
- $query = qq|
- SELECT i.id, i.qty, i.allocated, a.transdate
- FROM invoice i
- JOIN parts p ON (p.id = i.parts_id)
- JOIN ap a ON (a.id = i.trans_id)
- WHERE i.parts_id = ? AND (i.qty + i.allocated) > 0 AND i.sellprice = ?
- ORDER BY transdate|;
- $sth = $dbh->prepare($query);
- $sth->execute( $form->{"id_$i"}, $form->{"sellprice_$i"}) || $form->dberror($query);
- while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- $form->db_parse_numeric(sth=>$sth, hashref => $ref);
- my $qty = $ref->{qty} + $ref->{allocated};
- if ( ( $qty - $totalqty ) > 0 ) { $qty = $totalqty; }
- # update allocated for sold item
- $form->update_balance( $dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty * -1 );
- $allocated += $qty;
- last if ( ( $totalqty -= $qty ) <= 0 );
- }
- # stop patch bug 1755355 ###############################################################################
- }
+ }
# add purchase to inventory
push @{ $form->{acc_trans}{lineitems} },
@@ -443,13 +430,6 @@
qq|id = $form->{"id_$i"}|,
$form->{"qty_$i"} )
unless $form->{shipped};
- my $cogs_sth = $dbh->prepare(
- 'SELECT * FROM cogs__add_for_ap(?, ?, ?)'
- );
- $cogs_sth->execute($form->{"id_$i"},
- $form->{"qty_$i"},
- $form->{"sellprice_$i"});
- $cogs_sth->finish;
}
else {
@@ -761,6 +741,8 @@
# set values which could be empty
$form->{taxincluded} *= 1;
+ my $approved = 1;
+ $approved = 0 if $form->{separate_duties};
# save AP record
$query = qq|
@@ -782,7 +764,8 @@
intnotes = ?,
curr = ?,
language_code = ?,
- ponumber = ?
+ ponumber = ?,
+ approved = ?
WHERE id = ?|;
$sth = $dbh->prepare($query);
@@ -793,7 +776,8 @@
$form->{duedate}, $form->{shippingpoint}, $form->{shipvia},
$form->{taxincluded}, $form->{notes}, $form->{intnotes},
$form->{currency},
- $form->{language_code}, $form->{ponumber}, $form->{id}
+ $form->{language_code}, $form->{ponumber},
+ $approved, $form->{id}
) || $form->dberror($query);
# add shipto
@@ -809,6 +793,10 @@
id => $form->{id}
);
+ if (!$form->{separate_duties}){
+ $self->add_cogs($form);
+ }
+
$form->audittrail( $dbh, "", \%audittrail );
foreach $item ( keys %updparts ) {
Modified: trunk/LedgerSMB/IS.pm
===================================================================
--- trunk/LedgerSMB/IS.pm 2012-05-25 07:58:46 UTC (rev 4778)
+++ trunk/LedgerSMB/IS.pm 2012-05-25 10:38:59 UTC (rev 4779)
@@ -44,6 +44,16 @@
my $logger = Log::Log4perl->get_logger('LedgerSMB::IS');
+
+sub add_cogs {
+ my ($self, $form) = @_;
+ my $dbh = $form->{dbh};
+ my $query =
+ "select cogs__add_for_ar_line(id) FROM invoice WHERE trans_id = ?";
+ my $sth = $dbh->prepare($query) || $form->dberror($query);
+ $sth->execute($form->{id}) || $form->dberror($query);
+}
+
sub getposlines {
my ( $self, $myconfig, $form ) = @_;
%pos_config = %{ $form->{pos_config} };
@@ -1203,12 +1213,6 @@
$form->{"qty_$i"} * -1
) unless $form->{shipped};
- $allocated = cogs(
- $dbh, $form,
- $form->{"id_$i"}, $form->{"qty_$i"},
- $project_id, $form->{"sellprice_$i"},
- );
-
}
}
@@ -1594,6 +1598,8 @@
$form->{taxincluded} *= 1;
+ my $approved = 1;
+ $approved = 0 if $form->{separate_duties};
# save AR record
$query = qq|
UPDATE ar set
@@ -1618,7 +1624,8 @@
person_id = ?,
till = ?,
language_code = ?,
- ponumber = ?
+ ponumber = ?,
+ approved = ?
WHERE id = ?
|;
$sth = $dbh->prepare($query);
@@ -1633,7 +1640,7 @@
$form->{intnotes}, $form->{taxincluded},
$form->{currency},
$form->{employee_id}, $form->{till},
- $form->{language_code}, $form->{ponumber},
+ $form->{language_code}, $form->{ponumber}, $approved,
$form->{id}
) || $form->dberror($query);
@@ -1645,6 +1652,10 @@
# save printed, emailed and queued
$form->save_status($dbh);
+ if (!$form->{separate_duties}){
+ $self->add_cogs($form);
+ }
+
my %audittrail = (
tablename => 'ar',
reference => $form->{invnumber},
Property changes on: trunk/LedgerSMB/Scripts/account.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/account.pm:4369-4756
/branches/1.3/scripts/account.pl:3711-4368
+ /branches/1.3/LedgerSMB/Scripts/account.pm:4369-4777
/branches/1.3/scripts/account.pl:3711-4368
Property changes on: trunk/LedgerSMB/Scripts/admin.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/admin.pm:3901-4756
/branches/1.3/scripts/admin.pl:3711-4678
+ /branches/1.3/LedgerSMB/Scripts/admin.pm:3901-4777
/branches/1.3/scripts/admin.pl:3711-4678
Property changes on: trunk/LedgerSMB/Scripts/customer.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/customer.pm:4288-4756
/branches/1.3/scripts/customer.pl:4273-4287
+ /branches/1.3/LedgerSMB/Scripts/customer.pm:4288-4777
/branches/1.3/scripts/customer.pl:4273-4287
Property changes on: trunk/LedgerSMB/Scripts/employee.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/employee.pm:3712-4756
/branches/1.3/scripts/employee.pl:3842-3843,4273-4287,4289-4310
+ /branches/1.3/LedgerSMB/Scripts/employee.pm:3712-4777
/branches/1.3/scripts/employee.pl:3842-3843,4273-4287,4289-4310
Property changes on: trunk/LedgerSMB/Scripts/file.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/file.pm:3711-4756
/branches/1.3/scripts/file.pl:3711-4138
+ /branches/1.3/LedgerSMB/Scripts/file.pm:3711-4777
/branches/1.3/scripts/file.pl:3711-4138
Property changes on: trunk/LedgerSMB/Scripts/journal.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/journal.pm:4288-4756
/branches/1.3/scripts/journal.pl:3711-4328
+ /branches/1.3/LedgerSMB/Scripts/journal.pm:4288-4777
/branches/1.3/scripts/journal.pl:3711-4328
Property changes on: trunk/LedgerSMB/Scripts/login.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/login.pm:4193-4756
/branches/1.3/scripts/login.pl:3711-4192
+ /branches/1.3/LedgerSMB/Scripts/login.pm:4193-4777
/branches/1.3/scripts/login.pl:3711-4192
Property changes on: trunk/LedgerSMB/Scripts/menu.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/menu.pm:4155-4756
/branches/1.3/scripts/menu.pl:3711-4192,4273-4287
+ /branches/1.3/LedgerSMB/Scripts/menu.pm:4155-4777
/branches/1.3/scripts/menu.pl:3711-4192,4273-4287
Property changes on: trunk/LedgerSMB/Scripts/payment.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/payment.pm:4010-4756
/branches/1.3/scripts/payment.pl:3711-4680
+ /branches/1.3/LedgerSMB/Scripts/payment.pm:4010-4777
/branches/1.3/scripts/payment.pl:3711-4680
Property changes on: trunk/LedgerSMB/Scripts/recon.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/recon.pm:3711-4756
/branches/1.3/scripts/recon.pl:4194-4271,4273-4287,4393-4438
+ /branches/1.3/LedgerSMB/Scripts/recon.pm:3711-4777
/branches/1.3/scripts/recon.pl:4194-4271,4273-4287,4393-4438
Property changes on: trunk/LedgerSMB/Scripts/setup.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/setup.pm:3937-4756
/branches/1.3/scripts/setup.pl:3711-4550
+ /branches/1.3/LedgerSMB/Scripts/setup.pm:3937-4777
/branches/1.3/scripts/setup.pl:3711-4550
Property changes on: trunk/LedgerSMB/Scripts/taxform.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/taxform.pm:4193-4756
/branches/1.3/scripts/taxform.pl:3711-4192,4273-4287
+ /branches/1.3/LedgerSMB/Scripts/taxform.pm:4193-4777
/branches/1.3/scripts/taxform.pl:3711-4192,4273-4287
Property changes on: trunk/LedgerSMB/Scripts/vendor.pm
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/LedgerSMB/Scripts/vendor.pm:4288-4756
/branches/1.3/scripts/vendor.pl:4273-4287
+ /branches/1.3/LedgerSMB/Scripts/vendor.pm:4288-4777
/branches/1.3/scripts/vendor.pl:4273-4287
Modified: trunk/UI/Reports/filters/gl.html
===================================================================
--- trunk/UI/Reports/filters/gl.html 2012-05-25 07:58:46 UTC (rev 4778)
+++ trunk/UI/Reports/filters/gl.html 2012-05-25 10:38:59 UTC (rev 4779)
@@ -4,7 +4,7 @@
<?lsmb PROCESS 'elements.html' ?>
<body>
-<form method="get" action="<?lsmb script ?>">
+<form method="get" action="journal.pl">
<?lsmb PROCESS input element_data = {
type = "hidden"
name = "sort"
Modified: trunk/bin/ir.pl
===================================================================
--- trunk/bin/ir.pl 2012-05-25 07:58:46 UTC (rev 4778)
+++ trunk/bin/ir.pl 2012-05-25 10:38:59 UTC (rev 4779)
@@ -414,7 +414,8 @@
$form->{vc} = "vendor";
$form->hide_form(
qw(id title vc type terms creditlimit creditremaining closedto locked
- shipped oldtransdate recurring reverse batch_id subtype form_id)
+ shipped oldtransdate recurring reverse batch_id subtype form_id
+ separate_duties)
);
print qq|
Modified: trunk/bin/is.pl
===================================================================
--- trunk/bin/is.pl 2012-05-25 07:58:46 UTC (rev 4778)
+++ trunk/bin/is.pl 2012-05-25 10:38:59 UTC (rev 4779)
@@ -449,7 +449,7 @@
qw(form_id id type printed emailed queued title vc terms discount
creditlimit creditremaining tradediscount business closedto locked
shipped oldtransdate recurring reverse batch_id subtype
- meta_number)
+ meta_number separate_duties)
);
if ($form->{notice}){
Modified: trunk/sql/modules/COGS.sql
===================================================================
--- trunk/sql/modules/COGS.sql 2012-05-25 07:58:46 UTC (rev 4778)
+++ trunk/sql/modules/COGS.sql 2012-05-25 10:38:59 UTC (rev 4779)
@@ -74,7 +74,7 @@
WHERE qty + allocated < 0
ORDER BY a.transdate, a.id, i.id
LOOP
- IF t_alloc > qty THEN
+ IF t_alloc > in_qty THEN
RAISE EXCEPTION 'TOO MANY ALLOCATED';
ELSIF t_alloc = in_qty THEN
return t_cogs;
@@ -91,7 +91,7 @@
END IF;
END LOOP;
-RETURN 0;
+RETURN t_cogs;
END;
$$ LANGUAGE PLPGSQL;
@@ -115,7 +115,7 @@
WHERE allocated > 0
ORDER BY a.transdate, a.id, i.id
LOOP
- IF t_alloc > qty THEN
+ IF t_alloc > in_qty THEN
RAISE EXCEPTION 'TOO MANY ALLOCATED';
ELSIF t_alloc = in_qty THEN
return t_alloc;
@@ -230,4 +230,57 @@
END;
$$ LANGUAGE PLPGSQL;
+CREATE OR REPLACE FUNCTION cogs__add_for_ar_line(in_invoice_id int)
+RETURNS numeric AS
+$$
+DECLARE
+ t_cogs numeric;
+ t_inv invoice;
+ t_part parts;
+ t_ar ar;
+ t_transdate date;
+BEGIN
+
+SELECT CASE WHEN qty > 0 THEN cogs__add_for_ar(parts_id, qty)
+ ELSE cogs__reverse_ar(parts_id, qty)
+ END
+ INTO t_cogs
+ FROM invoice WHERE id = in_invoice_id;
+
+
+SELECT * INTO t_inv FROM invoice WHERE id = in_invoice_id;
+SELECT * INTO t_part FROM parts WHERE id = t_inv.parts_id;
+SELECT * INTO t_ar FROM ar WHERE id = t_inv.trans_id;
+
+SELECT CASE WHEN t_ar.transdate > max(end_date) THEN t_ar.transdate
+ ELSE max(end_date) + '1 day'::interval
+ END INTO t_transdate
+ from account_checkpoint td;
+
+INSERT INTO acc_trans
+ (trans_id, chart_id, approved, amount, transdate, invoice_id)
+VALUES (t_inv.trans_id, CASE WHEN t_inv.qty < 0 AND t_ar.is_return
+ THEN t_part.returns_accno_id
+ ELSE t_part.expense_accno_id
+ END, TRUE, t_cogs * -1, t_transdate, t_inv.id),
+ (t_inv.trans_id, t_part.inventory_accno_id, TRUE, t_cogs,
+ t_transdate, t_inv.id);
+
+RETURN t_cogs;
+
+END;
+
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION cogs__add_for_ap_line(in_invoice_id int)
+RETURNS numeric AS
+$$
+
+SELECT cogs__add_for_ap(i.parts_id, i.qty, p.lastcost)
+ FROM invoice i
+ JOIN parts p ON p.id = i.parts_id
+ WHERE i.id = $1;
+
+$$ LANGUAGE SQL;
+
COMMIT;
Modified: trunk/sql/modules/Drafts.sql
===================================================================
--- trunk/sql/modules/Drafts.sql 2012-05-25 07:58:46 UTC (rev 4778)
+++ trunk/sql/modules/Drafts.sql 2012-05-25 10:38:59 UTC (rev 4779)
@@ -69,8 +69,12 @@
SELECT table_name into t_table FROM transactions where id = in_id;
IF (t_table = 'ar') THEN
+ PERFORM cogs__add_for_ar_line(id) FROM invoice
+ WHERE trans_id = in_id;
UPDATE ar set approved = true where id = in_id;
ELSIF (t_table = 'ap') THEN
+ PERFORM cogs__add_for_ap_line(id) FROM invoice
+ WHERE trans_id = in_id;
UPDATE ap set approved = true where id = in_id;
ELSIF (t_table = 'gl') THEN
UPDATE gl set approved = true where id = in_id;
Property changes on: trunk/sql/upgrade/1.2-1.3-manual.sql
___________________________________________________________________
Modified: svn:mergeinfo
- /branches/1.3/sql/upgrade/1.2-1.3-manual.sql:3712-4756
/branches/1.3/sql/upgrade/1.2-1.3.sql:3711-3851
/trunk/sql/upgrade/1.2-1.3.sql:858-3710
+ /branches/1.3/sql/upgrade/1.2-1.3-manual.sql:3712-4777
/branches/1.3/sql/upgrade/1.2-1.3.sql:3711-3851
/trunk/sql/upgrade/1.2-1.3.sql:858-3710
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.