[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[5385] trunk
- Subject: SF.net SVN: ledger-smb:[5385] trunk
- From: ..hidden..
- Date: Wed, 19 Dec 2012 11:04:51 +0000
Revision: 5385
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5385&view=rev
Author: einhverfr
Date: 2012-12-19 11:04:50 +0000 (Wed, 19 Dec 2012)
Log Message:
-----------
Order consolidation renamed to combine orders, and moved to new code
Modified Paths:
--------------
trunk/LedgerSMB/OE.pm
trunk/LedgerSMB/Scripts/order.pm
trunk/bin/oe.pl
trunk/sql/Pg-database.sql
trunk/sql/modules/LOADORDER
trunk/sql/modules/OrderEntry.sql
trunk/sql/modules/menu_rebuild.sql
Modified: trunk/LedgerSMB/OE.pm
===================================================================
--- trunk/LedgerSMB/OE.pm 2012-12-19 08:17:39 UTC (rev 5384)
+++ trunk/LedgerSMB/OE.pm 2012-12-19 11:04:50 UTC (rev 5385)
@@ -2324,195 +2324,4 @@
}
-sub consolidate_orders {
- my ( $self, $myconfig, $form ) = @_;
-
- # connect to database
- my $dbh = $form->{dbh};
-
- my $i;
- my $id;
- my $ref;
- my %oe = ();
-
- my $query = qq|SELECT * FROM oe WHERE id = ?|;
- my $sth = $dbh->prepare($query) || $form->dberror($query);
-
- my $credit_account;
- my $oe_class_id;
- for ( $i = 1 ; $i <= $form->{rowcount} ; $i++ ) {
-
- # retrieve order
- if ( $form->{"ndx_$i"} ) {
- $sth->execute( $form->{"ndx_$i"} );
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
-
- $form->error( "Can't consolidate orders from different accounts" )
- if (defined( $credit_account )
- && ($credit_account != $ref->{entity_credit_account}));
- $credit_account = $ref->{entity_credit_account};
- $oe_class_id = $ref->{oe_class_id};
-
- $ref->{ndx} = $i;
- $oe{oe}{ $ref->{curr} }{ $ref->{id} } = $ref;
-
- $oe{vc}{ $ref->{curr} }{ $ref->{"$form->{vc}_id"} }++;
- $sth->finish;
- }
- }
-
- $query = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
- $sth = $dbh->prepare($query) || $form->dberror($query);
-
- foreach $curr ( keys %{ $oe{oe} } ) {
-
- foreach $id (
- sort { $oe{oe}{$curr}{$a}->{ndx} <=> $oe{oe}{$curr}{$b}->{ndx} }
- keys %{ $oe{oe}{$curr} }
- )
- {
-
- # retrieve order
- $vc_id = $oe{oe}{$curr}{$id}->{"$form->{vc}_id"};
-
- if ( $oe{vc}{ $oe{oe}{$curr}{$id}->{curr} }{$vc_id} > 1 ) {
-
- push @{ $oe{orders}{$curr}{$vc_id} }, $id;
-
- $sth->execute($id);
- while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- push @{ $oe{orderitems}{$curr}{$id} }, $ref;
- }
- $sth->finish;
-
- }
- }
- }
-
- my $ordnumber = $form->{ordnumber};
- my $numberfld = ( $form->{vc} eq 'customer' ) ? 'sonumber' : 'ponumber';
-
- my ( $department, $department_id ) = $form->{department};
- $department_id *= 1;
-
- my $uid = localtime;
- $uid .= "$$";
-
- my @orderitems = ();
-
- foreach $curr ( keys %{ $oe{orders} } ) {
-
- foreach $vc_id ( sort { $a <=> $b } keys %{ $oe{orders}{$curr} } ) {
-
- # the orders
- @orderitems = ();
- $form->{entity_id} = $vc_id;
- $amount = 0;
- $netamount = 0;
- my @orderids;
- my $orderid_str = "";
-
- foreach $id ( @{ $oe{orders}{$curr}{$vc_id} } ) {
- push(@orderids, $id);
- $orderid_str .= "?, ";
-
- # header
- $ref = $oe{oe}{$curr}{$id};
-
- $amount += $ref->{amount};
- $netamount += $ref->{netamount};
-
- $id = $dbh->quote($id);
- foreach $item ( @{ $oe{orderitems}{$curr}{$id} } ) {
-
- push @orderitems, $item;
- }
-
- # close order
- $query = qq|
- UPDATE oe SET
- closed = '1'
- WHERE id = $id|;
- $dbh->do($query) || $form->dberror($query);
-
- # reset shipped
- $query = qq|
- UPDATE orderitems SET
- ship = 0
- WHERE trans_id = $id|;
- $dbh->do($query) || $form->dberror($query);
- }
-
- $ordnumber ||=
- $form->update_defaults( $myconfig, $numberfld, $dbh, 1);
-
- #fixme: Change this
- #also $credit_account is safe since it is local to this function
- #and pulled from the db. Same with oe_class_id. --CT
- $query = qq|
- INSERT INTO oe (ordnumber, entity_credit_account, oe_class_id)
- VALUES ('$uid', $credit_account, $oe_class_id)|;
- $dbh->do($query) || $form->dberror($query);
-
- $query = qq|
- SELECT id
- FROM oe
- WHERE ordnumber = '$uid'|;
- ($id) = $dbh->selectrow_array($query);
-
- $ref->{employee_id} *= 1;
-
- $query = qq|
- UPDATE oe SET
- ordnumber = | . $dbh->quote($ordnumber) . qq|,
- transdate = current_date,
- amount = $amount,
- netamount = $netamount,
- reqdate = | . $form->dbquote( $ref->{reqdate}, SQL_DATE ) . qq|,
- taxincluded = |. $form->dbquote($ref->{taxincluded}) . qq|,
- shippingpoint = | . $dbh->quote( $ref->{shippingpoint} ) . qq|,
- notes = | . $dbh->quote( $ref->{notes} ) . qq|,
- curr = '$curr',
- person_id = | . $dbh->quote($ref->{person_id}) . qq|,
- intnotes = | . $dbh->quote( $ref->{intnotes} ) . qq|,
- shipvia = | . $dbh->quote( $ref->{shipvia} ) . qq|,
- language_code = '$ref->{language_code}',
- ponumber = | . $dbh->quote( $form->{ponumber} ) . qq|,
- department_id = $department_id
- WHERE id = $id|;
- $sth = $dbh->prepare($query);
- $sth->execute() || $form->dberror($query);
-
- $orderid_str =~ s/, $//;
-
- # add items
- $query = qq|
- INSERT INTO orderitems
- (trans_id, parts_id, description,
- qty, sellprice, discount, unit, reqdate,
- project_id, ship, serialnumber, notes,
- precision)
- SELECT ?, parts_id, description,
- qty, sellprice, discount, unit, reqdate,
- project_id, ship, serialnumber, notes,
- precision
- FROM orderitems
- WHERE trans_id IN ($orderid_str)|;
-
- $sth = $dbh->prepare($query);
- $sth->execute($id, @orderids) || $form->dberror($query);
-
-
- }
- }
-
- $rc = $dbh->commit;
-
- $rc;
-
-}
-
-
1;
-
Modified: trunk/LedgerSMB/Scripts/order.pm
===================================================================
--- trunk/LedgerSMB/Scripts/order.pm 2012-12-19 08:17:39 UTC (rev 5384)
+++ trunk/LedgerSMB/Scripts/order.pm 2012-12-19 11:04:50 UTC (rev 5385)
@@ -46,6 +46,7 @@
my $locale = $LedgerSMB::App_State::Locale;
$request->{entity_class} = $request->{oe_class_id} % 2 + 1;
$request->{report_name} = 'orders';
+ $request->{open} = 1 if $request->{search_type} ne 'search';
if ($request->{oe_class_id} == 1){
if ($request->{search_type} eq 'search'){
$request->{title} = $locale->text('Search Sales Orders');
@@ -60,6 +61,8 @@
} elsif ($request->{oe_class_id} == 2){
if ($request->{search_type} eq 'search'){
$request->{title} = $locale->text('Search Purchase Orders');
+ } elsif ($request->{search_type} eq 'combine'){
+ $request->{title} = $locale->text('Combine Purchase Orders');
} elsif ($request->{search_type} eq 'generate'){
$request->{title} =
$locale->text('Generate Purchase Orders from Sales Orders');
@@ -84,14 +87,52 @@
sub search {
my $request = shift @_;
- if ($request->{search_type} eq 'combine'
- or $request->{search_type} eq 'generate'){
+ if ($request->{search_type} ne 'search'){
$request->{selectable} = 1;
+ $request->{open} =1;
+ delete $request->{closed};
}
my $report = LedgerSMB::Report::Orders->new(%$request);
+ if ($request->{search_type} eq 'combine'){
+ $report->buttons([{
+ text => $LedgerSMB::App_State::Locale->text('Combine'),
+ type => 'submit',
+ class => 'submit',
+ name => 'action',
+ value => 'combine',
+ }]);
+ } elsif ($request->{search_type} eq 'generate'){
+ $report->buttons([{
+ text => $LedgerSMB::App_State::Locale->text('Generate'),
+ type => 'submit',
+ class => 'submit',
+ name => 'action',
+ value => 'generate',
+ }]);
+ }
$report->render(%request);
}
+=item combine
+
+This combines sales orders or purchase orders. It could be easily supported for
+quotations and rfq's but this is not currently allowed.
+
+=cut
+
+sub combine {
+ my ($request) = @_;
+ my @ids;
+ for (1 .. $request->{rowcount_}){
+ push @ids, $request->{"selected_$_"} if $request->{"selected_$_"};
+ }
+ $request->call_procedure(procname => 'order__combine', args => ..hidden..);
+ $request->{search_type} = 'combine';
+ get_criteria($request);
+}
+
+=item generate
+
=back
=head1 COPYRIGHT
Modified: trunk/bin/oe.pl
===================================================================
--- trunk/bin/oe.pl 2012-12-19 08:17:39 UTC (rev 5384)
+++ trunk/bin/oe.pl 2012-12-19 11:04:50 UTC (rev 5385)
@@ -2628,33 +2628,6 @@
}
-sub consolidate_orders {
-
- for ( 1 .. $form->{rowcount} ) {
- if ( $form->{"ndx_$_"} ) {
- $ok = 1;
- last;
- }
- }
-
- $form->error( $locale->text('Nothing selected!') ) unless $ok;
-
- ( $null, $argv ) = split /\?/, $form->{callback};
-
- for ( split /\&/, $argv ) {
- ( $key, $value ) = split /=/, $_;
- $form->{$key} = $value;
- }
-
- if ( OE->consolidate_orders( \%myconfig, \%$form ) ) {
- $form->redirect;
- }
- else {
- $form->error( $locale->text('Order generation failed!') );
- }
-
-}
-
sub select_vendor {
for ( 1 .. $form->{rowcount} ) {
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2012-12-19 08:17:39 UTC (rev 5384)
+++ trunk/sql/Pg-database.sql 2012-12-19 11:04:50 UTC (rev 5385)
@@ -2910,6 +2910,7 @@
-- Data for Name: menu_attribute; Type: TABLE DATA; Schema: public; Owner: postgres
--
+
COPY menu_attribute (node_id, attribute, value, id) FROM stdin;
205 menu 1 574
1 menu 1 1
@@ -2968,12 +2969,6 @@
57 type generate_sales_order 135
58 type generate_purchase_order 138
60 menu 1 550
-61 module oe.pl 140
-61 action search 141
-62 module oe.pl 143
-62 action search 144
-62 type consolidate_purchase_order 145
-61 type consolidate_sales_order 142
63 menu 1 146
64 module oe.pl 147
64 action search 148
@@ -3054,6 +3049,12 @@
55 search_type search 132
71 search_type search 165
72 search_type search 167
+61 action get_criteria 141
+62 action get_criteria 144
+62 search_type combine 145
+61 search_type combine 142
+61 module order.pl 140
+62 module order.pl 143
84 action stock_assembly 203
85 menu 1 204
86 module ic.pl 205
@@ -3510,9 +3511,10 @@
55 oe_class_id 2 68
71 oe_class_id 3 69
72 oe_class_id 4 70
+61 oe_class_id 1 38
+62 oe_class_id 2 41
\.
-
--
CREATE TABLE menu_acl (
Modified: trunk/sql/modules/LOADORDER
===================================================================
--- trunk/sql/modules/LOADORDER 2012-12-19 08:17:39 UTC (rev 5384)
+++ trunk/sql/modules/LOADORDER 2012-12-19 11:04:50 UTC (rev 5385)
@@ -36,3 +36,4 @@
Inventory_Report.sql
Payroll.sql
Transaction_Templates.sql
+OrderEntry.sql
Modified: trunk/sql/modules/OrderEntry.sql
===================================================================
--- trunk/sql/modules/OrderEntry.sql 2012-12-19 08:17:39 UTC (rev 5384)
+++ trunk/sql/modules/OrderEntry.sql 2012-12-19 11:04:50 UTC (rev 5385)
@@ -96,4 +96,91 @@
$$;
+CREATE OR REPLACE FUNCTION order__combine(in_ids int[])
+RETURNS SETOF oe LANGUAGE PLPGSQL AS
+$$
+
+DECLARE retval oe;
+ ordercount int;
+ ids int[];
+ loop_info record;
+ settings text[];
+ my_person_id int;
+BEGIN
+
+SELECT id INTO my_person_id
+ FROM person
+ WHERE entity_id = person__get_my_entity_id();
+
+settings := ARRAY['sonumber', 'ponumber', 'sqnumber', 'rfqnumber'];
+ids := array[]::int[];
+
+-- This approach of looping through insert/select operations will break down
+-- if overly complex order consolidation jobs are run (think, hundreds of
+-- combined orders in the *output*
+--
+-- The tradeoff is that if we address the huge complex runs here, then we have
+-- the possibility of having to lock the whole table which poses other issues.
+-- For that reason, I am going with this approach for now. --CT
+
+FOR loop_info IN
+ SELECT max(id) as id, taxincluded, entity_credit_account, oe_class_id,
+ curr
+ FROM oe WHERE id = any(in_ids)
+ GROUP BY taxincluded, entity_credit_account, oe_class_id, curr
+LOOP
+
+INSERT INTO oe
+ (ordnumber, transdate, amount, netamount,
+ reqdate, taxincluded, shippingpoint, notes,
+ curr, person_id, closed, quotation,
+ quonumber, intnotes, shipvia, language_code,
+ ponumber, terms, oe_class_id, entity_credit_account)
+SELECT CASE WHEN oe_class_id IN (1, 2)
+ THEN setting_increment(settings[oe_class_id])
+ ELSE NULL
+ END, now()::date, sum(amount), sum(netamount),
+ min(reqdate), taxincluded, min(shippingpoint), '',
+ curr, my_person_id, false, false,
+ CASE WHEN oe_class_id IN (3, 4)
+ THEN setting_increment(settings[oe_class_id])
+ ELSE NULL
+ END, NULL, NULL, NULL,
+ null, min(terms), oe_class_id, entity_credit_account
+ FROM oe
+ WHERE id = any (in_ids)
+ AND taxincluded = loop_info.taxincluded
+ AND entity_credit_account = loop_info.entity_credit_account
+ AND oe_class_id = loop_info.oe_class_id
+ GROUP BY curr, taxincluded, oe_class_id, entity_credit_account;
+
+
+INSERT INTO orderitems
+ (trans_id, parts_id, description, qty,
+ sellprice, precision, discount, unit,
+ reqdate, ship, serialnumber, notes)
+SELECT currval('oe_id_seq'), oi.parts_id, oi.description, oi.qty,
+ oi.sellprice, oi.precision, oi.discount, oi.unit,
+ oi.reqdate, oi.ship, oi.serialnumber, oi.notes
+ FROM orderitems oi
+ JOIN oe ON oi.trans_id = oe.id
+ WHERE oe.id = any (in_ids)
+ AND taxincluded = loop_info.taxincluded
+ AND entity_credit_account = loop_info.entity_credit_account
+ AND oe_class_id = loop_info.oe_class_id;
+
+ids := ids || currval('oe_id_seq')::int;
+
+END LOOP;
+
+UPDATE oe SET closed = true WHERE id = any(in_ids);
+
+FOR retval IN select * from oe WHERE id =any(ids)
+LOOP
+ RETURN NEXT retval;
+END LOOP;
+
+END;
+$$;
+
COMMIT;
Modified: trunk/sql/modules/menu_rebuild.sql
===================================================================
--- trunk/sql/modules/menu_rebuild.sql 2012-12-19 08:17:39 UTC (rev 5384)
+++ trunk/sql/modules/menu_rebuild.sql 2012-12-19 11:04:50 UTC (rev 5385)
@@ -211,12 +211,6 @@
57 type generate_sales_order 135
58 type generate_purchase_order 138
60 menu 1 550
-61 module oe.pl 140
-61 action search 141
-62 module oe.pl 143
-62 action search 144
-62 type consolidate_purchase_order 145
-61 type consolidate_sales_order 142
63 menu 1 146
64 module oe.pl 147
64 action search 148
@@ -297,6 +291,12 @@
55 search_type search 132
71 search_type search 165
72 search_type search 167
+61 action get_criteria 141
+62 action get_criteria 144
+62 search_type combine 145
+61 search_type combine 142
+61 module order.pl 140
+62 module order.pl 143
84 action stock_assembly 203
85 menu 1 204
86 module ic.pl 205
@@ -753,6 +753,8 @@
55 oe_class_id 2 68
71 oe_class_id 3 69
72 oe_class_id 4 70
+61 oe_class_id 1 38
+62 oe_class_id 2 41
\.
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.