kivitendo/sql/Pg-upgrade2/invoice_positions.pl @ master
| 942291e4 | Bernd Bleßmann | # @tag: invoice_positions
|
||
# @description: Spalte für Positionen der Einträge in Rechnungen
|
||||
# @depends: release_3_1_0
|
||||
package SL::DBUpgrade2::invoice_positions;
|
||||
use strict;
|
||||
use utf8;
|
||||
use parent qw(SL::DBUpgrade2::Base);
|
||||
sub run {
|
||||
my ($self) = @_;
|
||||
my $query = qq|ALTER TABLE invoice ADD position INTEGER|;
|
||||
$self->db_query($query);
|
||||
$query = qq|SELECT * FROM invoice ORDER BY trans_id, id|;
|
||||
| 0630567f | Sven Schöling | my $query2 = qq|UPDATE invoice SET position = ? WHERE id = ?|;
|
||
| 942291e4 | Bernd Bleßmann | |||
my $sth = $self->dbh->prepare($query);
|
||||
| 0630567f | Sven Schöling | my $sth2 = $self->dbh->prepare($query2);
|
||
| 942291e4 | Bernd Bleßmann | $sth->execute || $::form->dberror($query);
|
||
| 77b037dd | Bernd Bleßmann | # set new position field in order of ids, starting by one for each invoice
|
||
| 942291e4 | Bernd Bleßmann | my $last_invoice_id;
|
||
my $position;
|
||||
while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
|
||||
if ($ref->{trans_id} != $last_invoice_id) {
|
||||
$position = 1;
|
||||
} else {
|
||||
$position++;
|
||||
}
|
||||
$last_invoice_id = $ref->{trans_id};
|
||||
| 0630567f | Sven Schöling | $sth2->execute($position, $ref->{id});
|
||
| 942291e4 | Bernd Bleßmann | }
|
||
$sth->finish;
|
||||
| 0630567f | Sven Schöling | $sth2->finish;
|
||
| 942291e4 | Bernd Bleßmann | |||
$query = qq|ALTER TABLE invoice ALTER COLUMN position SET NOT NULL|;
|
||||
$self->db_query($query);
|
||||
return 1;
|
||||
}
|
||||
1;
|