X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FFU.pm;h=822e31fe32b1f204f95eedf24b2f6665f27acacf;hb=95b5d54bac9dc0cb47c67444c9e19c1d68b0d520;hp=bd761913c2e5ca0b5dffe4f30bdc379e67cfeea7;hpb=7a7f33b5c1b3531ab761adba1cf19d4ad68cffcd;p=kivitendo-erp.git diff --git a/SL/FU.pm b/SL/FU.pm index bd761913c..822e31fe3 100644 --- a/SL/FU.pm +++ b/SL/FU.pm @@ -6,18 +6,29 @@ use List::Util qw(first); use SL::Common; use SL::DBUtils; +use SL::DB; use SL::Notes; +use strict; + sub save { + my ($self, %params) = @_; $main::lxdebug->enter_sub(); + my $rc = SL::DB->client->with_transaction(\&_save, $self, %params); + + $::lxdebug->leave_sub; + return $rc; +} + +sub _save { my $self = shift; my %params = @_; my $myconfig = \%main::myconfig; my $form = $main::form; - my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + my $dbh = $params{dbh} || SL::DB->client->dbh; my ($query, @values); if (!$params{id}) { @@ -26,7 +37,7 @@ sub save { $query = qq|INSERT INTO follow_ups (created_by, done, note_id, follow_up_date, created_for_user, id) VALUES ((SELECT id FROM employee WHERE login = ?), ?, ?, ?, ?, ?)|; - push @values, $form->{login}; + push @values, $::myconfig{login}; } else { $query = qq|UPDATE follow_ups SET done = ?, note_id = ?, follow_up_date = ?, created_for_user = ? WHERE id = ?|; @@ -36,7 +47,8 @@ sub save { 'trans_id' => $params{id}, 'trans_module' => 'fu', 'subject' => $params{subject}, - 'body' => $params{body}); + 'body' => $params{body}, + 'dbh' => $dbh,); $params{done} = 1 if (!defined $params{done}); @@ -44,7 +56,7 @@ sub save { do_query($form, $dbh, qq|DELETE FROM follow_up_links WHERE follow_up_id = ?|, conv_i($params{id})); - my $query = qq|INSERT INTO follow_up_links (follow_up_id, trans_id, trans_type, trans_info) VALUES (?, ?, ?, ?)|; + $query = qq|INSERT INTO follow_up_links (follow_up_id, trans_id, trans_type, trans_info) VALUES (?, ?, ?, ?)|; my $sth = prepare_query($form, $dbh, $query); foreach my $link (@{ $params{LINKS} }) { @@ -53,9 +65,7 @@ sub save { $sth->finish(); - $dbh->commit() unless ($params{dbh}); - - $main::lxdebug->leave_sub(); + return 1; } sub finish { @@ -69,11 +79,10 @@ sub finish { my $myconfig = \%main::myconfig; my $form = $main::form; - my $dbh = $form->get_standard_dbh($myconfig); - - do_query($form, $dbh, qq|UPDATE follow_ups SET done = TRUE WHERE id = ?|, conv_i($params{id})); - - $dbh->commit(); + SL::DB->client->with_transaction(sub { + do_query($form, SL::DB->client->dbh, qq|UPDATE follow_ups SET done = TRUE WHERE id = ?|, conv_i($params{id})); + 1; + }) or do { die SL::DB->client->error }; $main::lxdebug->leave_sub(); } @@ -89,15 +98,16 @@ sub delete { my $myconfig = \%main::myconfig; my $form = $main::form; - my $dbh = $form->get_standard_dbh($myconfig); - - my $id = conv_i($params{id}); + SL::DB->client->with_transaction(sub { + my $dbh = SL::DB->client->dbh; - do_query($form, $dbh, qq|DELETE FROM follow_up_links WHERE follow_up_id = ?|, $id); - do_query($form, $dbh, qq|DELETE FROM follow_ups WHERE id = ?|, $id); - do_query($form, $dbh, qq|DELETE FROM notes WHERE (trans_id = ?) AND (trans_module = 'fu')|, $id); + my $id = conv_i($params{id}); - $dbh->commit(); + do_query($form, $dbh, qq|DELETE FROM follow_up_links WHERE follow_up_id = ?|, $id); + do_query($form, $dbh, qq|DELETE FROM follow_ups WHERE id = ?|, $id); + do_query($form, $dbh, qq|DELETE FROM notes WHERE (trans_id = ?) AND (trans_module = 'fu')|, $id); + 1; + }) or do { die SL::DB->client->error }; $main::lxdebug->leave_sub(); } @@ -116,7 +126,7 @@ sub retrieve { my $dbh = $form->get_standard_dbh($myconfig); my ($query, @values); - my ($employee_id) = selectrow_query($form, $dbh, qq|SELECT id FROM employee WHERE login = ?|, $form->{login}); + my ($employee_id) = selectrow_query($form, $dbh, qq|SELECT id FROM employee WHERE login = ?|, $::myconfig{login}); $query = qq|SELECT fu.*, n.subject, n.body, n.created_by FROM follow_ups fu LEFT JOIN notes n ON (fu.note_id = n.id) @@ -180,12 +190,15 @@ sub follow_ups { my $dbh = $form->get_standard_dbh($myconfig); my ($query, $where, $where_user); - my ($employee_id) = selectrow_query($form, $dbh, qq|SELECT id FROM employee WHERE login = ?|, $form->{login}); - my @values = ($employee_id, $employee_id); + my ($employee_id) = selectrow_query($form, $dbh, qq|SELECT id FROM employee WHERE login = ?|, $::myconfig{login}); + my @values = (); + my @values_user = (); if ($params{trans_id}) { - $where .= qq| AND EXISTS (SELECT * FROM follow_up_links ful - WHERE (ful.follow_up_id = fu.id) AND (ful.trans_id = ?))|; + $where .= qq| AND fu.id IN (select follow_up_id from follow_up_links where trans_id = ?)|; + # $where .= qq| AND (ful.follow_up_id = fu.id) AND (ful.trans_id = ?))|; + # $where .= qq| AND EXISTS (SELECT * FROM follow_up_links ful + # WHERE (ful.follow_up_id = fu.id) AND (ful.trans_id = ?))|; push @values, conv_i($params{trans_id}); } @@ -206,7 +219,7 @@ sub follow_ups { foreach my $item (qw(subject body)) { next unless ($params{$item}); $where .= qq| AND (n.${item} ILIKE ?)|; - push @values, '%' . $params{$item} . '%'; + push @values, like($params{$item}); } if ($params{reference}) { @@ -215,7 +228,7 @@ sub follow_ups { WHERE (ful.follow_up_id = fu.id) AND (ful.trans_info ILIKE ?) LIMIT 1)|; - push @values, '%' . $params{reference} . '%'; + push @values, like($params{reference}); } if ($params{follow_up_date_from}) { @@ -235,10 +248,28 @@ sub follow_ups { $where .= qq| AND (date_trunc('DAY', fu.itime) <= ?)|; push @values, conv_date($params{itime_to}); } + if ($params{created_for}) { + $where .= qq| AND fu.created_for_user = ?|; + push @values, conv_i($params{created_for}); + } - if ($params{all_users}) { + if ($params{all_users} || $params{trans_id}) { # trans_id only for documents? $where_user = qq|OR (fu.created_by IN (SELECT DISTINCT what FROM follow_up_access WHERE who = ?))|; - push @values, $employee_id; + push @values_user, $employee_id; + } + + my $order_by = ''; + + if ($form->{sort} ne 'title') { + my %sort_columns = ( + 'follow_up_date' => [ qw(fu.follow_up_date fu.id) ], + 'created_on' => [ qw(created_on fu.id) ], + 'subject' => [ qw(lower(n.subject)) ], + ); + + my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC'; + my $sortkey = $sort_columns{$form->{sort}} ? $form->{sort} : 'follow_up_date'; + $order_by = 'ORDER BY ' . join(', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} }); } $query = qq|SELECT fu.*, n.subject, n.body, n.created_by, @@ -253,9 +284,9 @@ sub follow_ups { WHERE ((fu.created_by = ?) OR (fu.created_for_user = ?) $where_user) $where - ORDER BY fu.follow_up_date DESC, fu.id ASC|; + $order_by|; - my $follow_ups = selectall_hashref_query($form, $dbh, $query, @values); + my $follow_ups = selectall_hashref_query($form, $dbh, $query, $employee_id, $employee_id, @values_user, @values); if (!scalar @{ $follow_ups }) { $main::lxdebug->leave_sub(); @@ -266,6 +297,14 @@ sub follow_ups { $fu->{LINKS} = $self->retrieve_links(%{ $fu }); } + if ($form->{sort} eq 'title') { + my $dir_factor = !defined $form->{sortdir} ? 1 : $form->{sortdir} ? 1 : -1; + $follow_ups = [ map { $_->[1] } + sort { ($a->[0] cmp $b->[0]) * $dir_factor } + map { my $fu = $follow_ups->[$_]; [ @{ $fu->{LINKS} } ? lc($fu->{LINKS}->[0]->{title}) : '', $fu ] } + (0 .. scalar(@{ $follow_ups }) - 1) ]; + } + $main::lxdebug->leave_sub(); return $follow_ups; @@ -288,25 +327,51 @@ sub link_details { if ($params{trans_type} eq 'customer') { $link = { - 'url' => 'ct.pl?action=edit&db=customer&id=' . $form->quote($params{trans_id}) . '&edit_note_id=' . $form->quote($params{note_id}), + 'url' => 'controller.pl?action=CustomerVendor/edit&db=customer&id=' . $form->quote($params{trans_id}) . '¬e_id=' . $form->quote($params{note_id}), 'title' => $locale->text('Customer') . " '$params{trans_info}'", }; } elsif ($params{trans_type} eq 'vendor') { $link = { - 'url' => 'ct.pl?action=edit&type=sales_quotation&id=' . $params{trans_id} . '&edit_note_id=' . $form->quote($params{note_id}), + 'url' => 'controller.pl?action=CustomerVendor/edit&db=vendor&id=' . $params{trans_id} . '¬e_id=' . $form->quote($params{note_id}), 'title' => $locale->text('Vendor') . " '$params{trans_info}'", }; } elsif ($params{trans_type} eq 'sales_quotation') { + my $script = 'oe.pl'; + my $action = 'edit'; + if ($::instance_conf->get_feature_experimental_order) { + $script = 'controller.pl'; + $action = 'Order/edit'; + } $link = { - 'url' => 'oe.pl?action=edit&type=sales_quotation&id=' . $params{trans_id}, + 'url' => $script . '?action=' . $action . '&type=sales_quotation&id=' . $params{trans_id}, 'title' => $locale->text('Sales quotation') . " $params{trans_info}", }; + } elsif ($params{trans_type} eq 'sales_delivery_order') { + + $link = { + 'url' => 'do.pl?action=edit&type=sales_delivery_order&id=' . $params{trans_id} . '&edit_note_id=' . $form->quote($params{note_id}), + 'title' => $locale->text('Sales delivery order') .' '. $params{trans_info}, + }; + + } elsif ($params{trans_type} eq 'purchase_delivery_order') { + + $link = { + 'url' => 'do.pl?action=edit&type=purchase_delivery_order&id=' . $params{trans_id} . '&edit_note_id=' . $form->quote($params{note_id}), + 'title' => $locale->text('Purchase delivery order') .' '. $params{trans_info}, + }; + } elsif ($params{trans_type} eq 'sales_order') { + my $script = 'oe.pl'; + my $action = 'edit'; + if ($::instance_conf->get_feature_experimental_order) { + $script = 'controller.pl'; + $action = 'Order/edit'; + } $link = { - 'url' => 'oe.pl?action=edit&type=sales_order&id=' . $params{trans_id}, + 'url' => $script . '?action=' . $action . '&type=sales_order&id=' . $params{trans_id}, 'title' => $locale->text('Sales Order') . " $params{trans_info}", }; @@ -316,6 +381,12 @@ sub link_details { 'title' => $locale->text('Sales Invoice') . " $params{trans_info}", }; + } elsif ($params{trans_type} eq 'purchase_invoice') { + $link = { + 'url' => 'ir.pl?action=edit&type=purchase_invoice&id=' . $params{trans_id}, + 'title' => $locale->text('Purchase Invoice') . " $params{trans_info}", + }; + } elsif ($params{trans_type} eq 'credit_note') { $link = { 'url' => 'is.pl?action=edit&type=credit_note&id=' . $params{trans_id}, @@ -329,14 +400,26 @@ sub link_details { }; } elsif ($params{trans_type} eq 'request_quotation') { + my $script = 'oe.pl'; + my $action = 'edit'; + if ($::instance_conf->get_feature_experimental_order) { + $script = 'controller.pl'; + $action = 'Order/edit'; + } $link = { - 'url' => 'oe.pl?action=edit&type=request_quotation&id=' . $params{trans_id}, + 'url' => $script . '?action=' . $action . '&type=request_quotation&id=' . $params{trans_id}, 'title' => $locale->text('Request quotation') . " $params{trans_info}", }; } elsif ($params{trans_type} eq 'purchase_order') { + my $script = 'oe.pl'; + my $action = 'edit'; + if ($::instance_conf->get_feature_experimental_order) { + $script = 'controller.pl'; + $action = 'Order/edit'; + } $link = { - 'url' => 'oe.pl?action=edit&type=purchase_order&id=' . $params{trans_id}, + 'url' => $script . '?action=' . $action . '&type=purchase_order&id=' . $params{trans_id}, 'title' => $locale->text('Purchase Order') . " $params{trans_info}", }; @@ -348,13 +431,13 @@ sub link_details { } elsif ($params{trans_type} eq 'ar_transaction') { $link = { - 'url' => 'ar.pl?action=editid=' . $params{trans_id}, + 'url' => 'ar.pl?action=edit&id=' . $params{trans_id}, 'title' => $locale->text('AR Transaction') . " $params{trans_info}", }; } elsif ($params{trans_type} eq 'ap_transaction') { $link = { - 'url' => 'ap.pl?action=editid=' . $params{trans_id}, + 'url' => 'ap.pl?action=edit&id=' . $params{trans_id}, 'title' => $locale->text('AP Transaction') . " $params{trans_info}", }; @@ -382,24 +465,25 @@ sub save_access_rights { my $myconfig = \%main::myconfig; my $form = $main::form; - my $dbh = $form->get_standard_dbh($myconfig); - - my ($id) = selectrow_query($form, $dbh, qq|SELECT id FROM employee WHERE login = ?|, $form->{login}); + SL::DB->client->with_transaction(sub { + my $dbh = SL::DB->client->dbh; - my $query = qq|INSERT INTO follow_up_access (who, what) VALUES (?, ?)|; - my $sth = prepare_query($form, $dbh, $query); + my ($id) = selectrow_query($form, $dbh, qq|SELECT id FROM employee WHERE login = ?|, $::myconfig{login}); - do_query($form, $dbh, qq|DELETE FROM follow_up_access WHERE what = ?|, $id); + do_query($form, $dbh, qq|DELETE FROM follow_up_access WHERE what = ?|, $id); - while (my ($who, $access_allowed) = each %{ $params{access} }) { - next unless ($access_allowed); + my $query = qq|INSERT INTO follow_up_access (who, what) VALUES (?, ?)|; + my $sth = prepare_query($form, $dbh, $query); - do_statement($form, $sth, $query, conv_i($who), $id); - } + while (my ($who, $access_allowed) = each %{ $params{access} }) { + next unless ($access_allowed); - $sth->finish(); + do_statement($form, $sth, $query, conv_i($who), $id); + } - $dbh->commit(); + $sth->finish(); + 1; + }) or do { die SL::DB->client->error }; $main::lxdebug->leave_sub(); } @@ -415,7 +499,7 @@ sub retrieve_access_rights { my $dbh = $form->get_standard_dbh($myconfig); - my $sth = prepare_execute_query($form, $dbh, qq|SELECT who FROM follow_up_access WHERE what = (SELECT id FROM employee WHERE login = ?)|, $form->{login}); + my $sth = prepare_execute_query($form, $dbh, qq|SELECT who FROM follow_up_access WHERE what = (SELECT id FROM employee WHERE login = ?)|, $::myconfig{login}); my $access = {}; while (my $ref = $sth->fetchrow_hashref()) {