X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FForm.pm;h=6a1fa813cdd22c04255e7dcabceaa2426f0d07de;hb=a143b82a56e889cfda60c2d42fe7d0bdaf73aa83;hp=edf5de978038c3254fb921ce745626f5cc6ce1e1;hpb=7712480e4a082f84a2229f5b1a90d79a6c01255b;p=kivitendo-erp.git diff --git a/SL/Form.pm b/SL/Form.pm index edf5de978..6a1fa813c 100644 --- a/SL/Form.pm +++ b/SL/Form.pm @@ -44,6 +44,7 @@ use SL::Template; use CGI::Ajax; use SL::DBUtils; use SL::Menu; +use SL::User; use CGI; sub _input_to_hash { @@ -959,7 +960,7 @@ sub datetonum { # Database routines used throughout sub dbconnect { - $main::lxdebug->enter_sub(); + $main::lxdebug->enter_sub(2); my ($self, $myconfig) = @_; @@ -974,7 +975,7 @@ sub dbconnect { $dbh->do($myconfig->{dboptions}) || $self->dberror($myconfig->{dboptions}); } - $main::lxdebug->leave_sub(); + $main::lxdebug->leave_sub(2); return $dbh; } @@ -1377,6 +1378,35 @@ sub get_employee { $main::lxdebug->leave_sub(); } +sub get_salesman { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $salesman_id) = @_; + + my $dbh = $self->dbconnect($myconfig); + + my ($login) = + selectrow_query($self, $dbh, qq|SELECT login FROM employee WHERE id = ?|, + $salesman_id); + + if ($login) { + my $user = new User($main::memberfile, $login); + map({ $self->{"salesman_$_"} = $user->{$_}; } + qw(address businessnumber co_ustid company duns email fax name + taxnumber tel)); + $self->{salesman_login} = $login; + + $self->{salesman_name} = $login + if ($self->{salesman_name} eq ""); + + map({ $self->{"salesman_$_"} =~ s/\\n/\n/g; } qw(address company)); + } + + $dbh->disconnect(); + + $main::lxdebug->leave_sub(); +} + sub get_duedate { $main::lxdebug->enter_sub(); @@ -1578,6 +1608,18 @@ sub _get_taxcharts { $main::lxdebug->leave_sub(); } +sub _get_employees { + $main::lxdebug->enter_sub(); + + my ($self, $dbh, $key) = @_; + + $key = "all_employees" unless ($key); + $self->{$key} = + selectall_hashref_query($self, $dbh, qq|SELECT * FROM employee|); + + $main::lxdebug->leave_sub(); +} + sub get_lists { $main::lxdebug->enter_sub(); @@ -1616,6 +1658,10 @@ sub get_lists { $self->_get_taxcharts($dbh, $params{"taxcharts"}); } + if ($params{"employees"}) { + $self->_get_employees($dbh, $params{"employees"}); + } + $dbh->disconnect(); $main::lxdebug->leave_sub(); @@ -1691,7 +1737,7 @@ sub all_vc { # build selection list if ($count < $myconfig->{vclimit}) { - $query = qq|SELECT id, name + $query = qq|SELECT id, name, salesman_id FROM $table WHERE not obsolete ORDER BY name|; $sth = $dbh->prepare($query); @@ -1909,12 +1955,14 @@ sub create_links { # now get the account numbers $query = qq|SELECT c.accno, c.description, c.link, c.taxkey_id, tk.tax_id FROM chart c, taxkeys tk - WHERE c.link LIKE '%$module%' AND c.id=tk.chart_id AND tk.id = (SELECT id from taxkeys where taxkeys.chart_id =c.id AND startdate<=$transdate ORDER BY startdate desc LIMIT 1) + WHERE c.link LIKE '%$module%' AND c.id=tk.chart_id AND tk.id = + (SELECT id FROM taxkeys where taxkeys.chart_id = c.id AND startdate <= $transdate ORDER BY startdate desc LIMIT 1) ORDER BY c.accno|; $sth = $dbh->prepare($query); - $sth->execute || $self->dberror($query); - + + do_statement($form, $sth, $query); + $self->{accounts} = ""; while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { @@ -1937,43 +1985,17 @@ sub create_links { } # get taxkeys and description - $query = qq|SELECT id, taxkey, taxdescription - FROM tax|; - $sth = $dbh->prepare($query); - $sth->execute || $self->dberror($query); - - $ref = $sth->fetchrow_hashref(NAME_lc); - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $self->{TAXKEY} }, $ref; - } - - $sth->finish; - + $query = qq|SELECT id, taxkey, taxdescription FROM tax|; + $self->{TAXKEY} = selectall_hashref_query($form, $dbh, $query); # get tax zones - $query = qq|SELECT id, description - FROM tax_zones|; - $sth = $dbh->prepare($query); - $sth->execute || $self->dberror($query); - - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $self->{TAXZONE} }, $ref; - } - $sth->finish; + $query = qq|SELECT id, description FROM tax_zones|; + $self->{TAXZONE} = selectall_hashref_query($form, $dbh, $query); if (($module eq "AP") || ($module eq "AR")) { - # get tax rates and description $query = qq| SELECT * FROM tax t|; - $sth = $dbh->prepare($query); - $sth->execute || $self->dberror($query); - $self->{TAX} = (); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $self->{TAX} }, $ref; - } - $sth->finish; + $self->{TAX} = selectall_hashref_query($form, $dbh, $query); } if ($self->{id}) { @@ -1991,7 +2013,7 @@ sub create_links { LEFT JOIN department d ON (d.id = a.department_id) WHERE a.id = $self->{id}|; $sth = $dbh->prepare($query); - $sth->execute || $self->dberror($query); + do_statement($form, $sth, $query); $ref = $sth->fetchrow_hashref(NAME_lc); foreach $key (keys %$ref) { @@ -2006,13 +2028,30 @@ sub create_links { } # now get the account numbers - $query = qq|SELECT c.accno, c.description, c.link, c.taxkey_id, tk.tax_id - FROM chart c, taxkeys tk - WHERE c.link LIKE '%$module%' AND (((tk.chart_id=c.id) AND NOT(c.link like '%_tax%')) OR (NOT(tk.chart_id=c.id) AND (c.link like '%_tax%'))) AND (((tk.id = (SELECT id from taxkeys where taxkeys.chart_id =c.id AND startdate<=$transdate ORDER BY startdate desc LIMIT 1)) AND NOT(c.link like '%_tax%')) OR (c.link like '%_tax%')) - ORDER BY c.accno|; - + $query = qq| + SELECT + c.accno, + c.description, + c.link, + c.taxkey_id, + tk.tax_id + FROM chart c + LEFT JOIN taxkeys tk ON (tk.chart_id = c.id) + WHERE + c.link LIKE ? + AND + (tk.chart_id = c.id AND NOT c.link like '%_tax%') + OR (NOT tk.chart_id = c.id AND c.link like '%_tax%') + AND + tk.id = ( SELECT id from taxkeys + WHERE taxkeys.chart_id = c.id + AND startdate <= ? + ORDER BY startdate desc LIMIT 1 + ) + ORDER BY c.accno|; + $sth = $dbh->prepare($query); - $sth->execute || $self->dberror($query); + do_statement($form, $sth, $query, "%$module%", $transdate); $self->{accounts} = ""; while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { @@ -2046,7 +2085,7 @@ sub create_links { AND a.fx_transaction = '0' ORDER BY a.oid,a.transdate|; $sth = $dbh->prepare($query); - $sth->execute || $self->dberror($query); + do_statement($form, $sth, $query); my $fld = ($table eq 'customer') ? 'buy' : 'sell'; @@ -2080,7 +2119,7 @@ sub create_links { WHERE d.fxloss_accno_id = c.id) AS fxloss_accno FROM defaults d|; $sth = $dbh->prepare($query); - $sth->execute || $self->dberror($query); + do_statement($form, $sth, $query); $ref = $sth->fetchrow_hashref(NAME_lc); map { $self->{$_} = $ref->{$_} } keys %$ref; @@ -2097,7 +2136,7 @@ sub create_links { WHERE d.fxloss_accno_id = c.id) AS fxloss_accno FROM defaults d|; $sth = $dbh->prepare($query); - $sth->execute || $self->dberror($query); + do_statement($form, $sth, $query); $ref = $sth->fetchrow_hashref(NAME_lc); map { $self->{$_} = $ref->{$_} } keys %$ref; @@ -2482,9 +2521,14 @@ sub update_defaults { sub update_business { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $business_id) = @_; + my ($self, $myconfig, $business_id, $provided_dbh) = @_; - my $dbh = $self->dbconnect_noauto($myconfig); + my $dbh; + if ($provided_dbh) { + $dbh = $provided_dbh; + } else { + $dbh = $self->dbconnect_noauto($myconfig); + } my $query = qq|SELECT customernumberinit FROM business WHERE id=$business_id FOR UPDATE|; my $sth = $dbh->prepare($query); @@ -2499,36 +2543,16 @@ sub update_business { SET customernumberinit = '$var' WHERE id=$business_id|; $dbh->do($query) || $self->dberror($query); - $dbh->commit; - $dbh->disconnect; + if (!$provided_dbh) { + $dbh->commit; + $dbh->disconnect; + } $main::lxdebug->leave_sub(); return $var; } -sub get_salesman { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $salesman) = @_; - - my $dbh = $self->dbconnect($myconfig); - my $query = - qq|SELECT id, name FROM customer WHERE (customernumber ilike '%$salesman%' OR name ilike '%$salesman%') AND business_id in (SELECT id from business WHERE salesman)|; - my $sth = $dbh->prepare($query); - $sth->execute || $self->dberror($query); - - my $i = 0; - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - push(@{ $self->{salesman_list} }, $ref); - $i++; - } - $dbh->commit; - $main::lxdebug->leave_sub(); - - return $i; -} - sub get_partsgroup { $main::lxdebug->enter_sub();