Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision b8fee3fc

Von Niclas Zimmermann vor etwa 12 Jahren hinzugefügt

  • ID b8fee3fc5f7ae08e3d431c6c2fad4add9a2d88cf
  • Vorgänger ca9d536f
  • Nachfolger 56f7991d

Entfernt dpt_trans

Entfernt die Tabelle dpt_trans. Dazu mussten die SQL-queries in der
SL/RP.pm noch angepasst werden. Das Upgrate-Script entfernt zusätzlich
noch alle Trigger/Funktionen, die in die Tabelle dpt_trans noch benutzt
haben.

Unterschiede anzeigen:

SL/RP.pm
my $query;
my $dpt_where = '';
my $dpt_join = '';
my $dpt_where_without_arapgl = '';
my $project = '';
my $where = "1 = 1";
my $glwhere = "";
......
}
if ($department_id) {
$dpt_join = qq| JOIN department t ON (a.department_id = t.id) |;
$dpt_where = qq| AND (t.id = | . conv_i($department_id, 'NULL') . qq|)|;
$dpt_where = qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|)|;
}
if ($form->{project_id}) {
......
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
JOIN ar a ON (a.id = ac.trans_id)
$dpt_join
WHERE $where
$dpt_where
$category
......
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
JOIN ap a ON (a.id = ac.trans_id)
$dpt_join
WHERE $where
$dpt_where
$category
......
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
JOIN gl a ON (a.id = ac.trans_id)
$dpt_join
WHERE $where
$glwhere
$dpt_where
......
JOIN ar a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
JOIN chart c on (p.income_accno_id = c.id)
$dpt_join
-- use transdate from subwhere
WHERE (c.category = 'I')
$subwhere
......
JOIN ap a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
JOIN chart c on (p.expense_accno_id = c.id)
$dpt_join
WHERE (c.category = 'E')
$subwhere
$dpt_where
......
} else { # if ($form->{method} eq 'cash')
if ($department_id) {
$dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id) |;
$dpt_where = qq| AND t.department_id = | . conv_i($department_id);
$dpt_where = qq| AND a.department_id = | . conv_i($department_id);
$dpt_where_without_arapgl = qq| AND COALESCE((SELECT department_id FROM ar WHERE ar.id=ac.trans_id),
(SELECT department_id FROM gl WHERE gl.id=ac.trans_id),
(SELECT department_id FROM ap WHERE ap.id=ac.trans_id)) = | . conv_i($department_id);
}
$query = qq|
SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
$dpt_join
WHERE $where
$dpt_where
$dpt_where_without_arapgl
$category
$project
GROUP BY c.accno, c.description, c.category |;
......
JOIN ar a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
JOIN chart c on (p.income_accno_id = c.id)
$dpt_join
-- use transdate from subwhere
WHERE (c.category = 'I')
$subwhere
......
JOIN ap a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
JOIN chart c on (p.expense_accno_id = c.id)
$dpt_join
WHERE (c.category = 'E')
$subwhere
$dpt_where
......
my $query;
my $dpt_where;
my $dpt_join;
my $dpt_where_without_arapgl;
my $project;
my $where = "1 = 1";
my $glwhere = "";
......
}
if ($department_id) {
$dpt_join = qq| JOIN department t ON (a.department_id = t.id) |;
$dpt_where = qq| AND (t.id = | . conv_i($department_id, 'NULL') . qq|) |;
$dpt_where = qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
}
if ($form->{project_id}) {
......
FROM acc_trans acc
INNER JOIN chart c ON (acc.chart_id = c.id AND c.link LIKE '%AR_paid%')
WHERE 1=1 $inwhere AND acc.trans_id = ac.trans_id)
/ (SELECT amount FROM ar WHERE id = ac.trans_id)
ELSE 0
/ (SELECT amount FROM ar WHERE id = ac.trans_id)
ELSE 0
/* ar amount is zero, or we are checking with a non-ar-transaction, so we return 0 in both cases as multiplicator of ac.amount */
END
) AS amount, c.$category
......
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
JOIN ar a ON (a.id = ac.trans_id)
$dpt_join
WHERE $where $dpt_where
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AR_paid%') $subwhere)
$project
......
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
JOIN ap a ON (a.id = ac.trans_id)
$dpt_join
WHERE $where $dpt_where
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AP_paid%') $subwhere)
$project
......
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
JOIN gl a ON (a.id = ac.trans_id)
$dpt_join
WHERE $where $dpt_where $glwhere
AND NOT ((c.link = 'AR') OR (c.link = 'AP'))
$project
......
JOIN ar a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
JOIN chart c on (p.income_accno_id = c.id)
$dpt_join
WHERE (c.category = 'I') $prwhere $dpt_where
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AR_paid%') $subwhere)
$project
......
JOIN ap a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
JOIN chart c on (p.expense_accno_id = c.id)
$dpt_join
WHERE (c.category = 'E') $prwhere $dpt_where
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AP_paid%') $subwhere)
$project
......
} else { # if ($form->{method} eq 'cash')
if ($department_id) {
$dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id) |;
$dpt_where = qq| AND (t.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
$dpt_where = qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
$dpt_where_without_arapgl = qq| AND COALESCE((SELECT department_id FROM ar WHERE ar.id=ac.trans_id),
(SELECT department_id FROM gl WHERE gl.id=ac.trans_id),
(SELECT department_id FROM ap WHERE ap.id=ac.trans_id)) = | . conv_i($department_id);
}
$query = qq|
SELECT sum(ac.amount * chart_category_to_sgn(c.category)) AS amount, c.$category
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
$dpt_join
WHERE $where
$dpt_where
$dpt_where_without_arapgl
$project
GROUP BY c.$category |;
......
JOIN ar a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
JOIN chart c on (p.income_accno_id = c.id)
$dpt_join
WHERE (c.category = 'I')
$prwhere
$dpt_where
......
JOIN ap a ON (a.id = ac.trans_id)
JOIN parts p ON (ac.parts_id = p.id)
JOIN chart c on (p.expense_accno_id = c.id)
$dpt_join
WHERE (c.category = 'E')
$prwhere
$dpt_where
......
my ($null, $department_id) = split /--/, $form->{department};
my @headingaccounts = ();
my $dpt_where;
my $dpt_join;
my $dpt_where_without_arapgl;
my $project;
my $where = "1 = 1";
my $invwhere = $where;
if ($department_id) {
$dpt_join = qq| JOIN dpt_trans t ON (ac.trans_id = t.trans_id) |;
$dpt_where = qq| AND (t.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
$dpt_where = qq| AND (a.department_id = | . conv_i($department_id, 'NULL') . qq|) |;
$dpt_where_without_arapgl = qq| AND COALESCE((SELECT department_id FROM ar WHERE ar.id=ac.trans_id),
(SELECT department_id FROM gl WHERE gl.id=ac.trans_id),
(SELECT department_id FROM ap WHERE ap.id=ac.trans_id)) = | . conv_i($department_id);
}
# project_id only applies to getting transactions
......
my $min_max = $prefix eq 'from' ? 'min' : 'max';
$query = qq|SELECT ${min_max}(transdate)
FROM acc_trans ac
$dpt_join
WHERE (1 = 1)
$dpt_where
$dpt_where_without_arapgl
$project|;
($form->{"${prefix}date"}) = selectfirst_array_query($form, $dbh, $query);
}
......
qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, c.description
FROM acc_trans ac
LEFT JOIN chart c ON (ac.chart_id = c.id)
$dpt_join
WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction
$dpt_where
$dpt_where_without_arapgl
$project
GROUP BY c.accno, c.category, c.description |;
......
SELECT c.accno, c.description, c.category, SUM(ac.amount) AS amount
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
$dpt_join
WHERE $where
$dpt_where
$dpt_where_without_arapgl
$project
GROUP BY c.accno, c.description, c.category |;
......
JOIN ar a ON (ac.trans_id = a.id)
JOIN parts p ON (ac.parts_id = p.id)
JOIN chart c ON (p.income_accno_id = c.id)
$dpt_join
WHERE $invwhere
$dpt_where
$project
......
JOIN ap a ON (ac.trans_id = a.id)
JOIN parts p ON (ac.parts_id = p.id)
JOIN chart c ON (p.expense_accno_id = c.id)
$dpt_join
WHERE $invwhere
$dpt_where
$project
......
(SELECT SUM(ac.amount) * -1
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
$dpt_join
WHERE $where
$dpt_where
$dpt_where_without_arapgl
$project
AND (ac.amount < 0)
AND (c.accno = ?)) AS debit,
......
(SELECT SUM(ac.amount)
FROM acc_trans ac
JOIN chart c ON (c.id = ac.chart_id)
$dpt_join
WHERE $where
$dpt_where
$dpt_where_without_arapgl
$project
AND ac.amount > 0
AND c.accno = ?) AS credit,
(SELECT SUM(ac.amount)
FROM acc_trans ac
JOIN chart c ON (ac.chart_id = c.id)
$dpt_join
WHERE $saldowhere
$dpt_where
$dpt_where_without_arapgl
$project
AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo,
(SELECT SUM(ac.amount)
FROM acc_trans ac
JOIN chart c ON (ac.chart_id = c.id)
$dpt_join
WHERE $sumwhere
$dpt_where
$dpt_where_without_arapgl
$project
AND amount > 0
AND c.accno = ?) AS sum_credit,
......
(SELECT SUM(ac.amount)
FROM acc_trans ac
JOIN chart c ON (ac.chart_id = c.id)
$dpt_join
WHERE $sumwhere
$dpt_where
$dpt_where_without_arapgl
$project
AND amount < 0
AND c.accno = ?) AS sum_debit,
(SELECT max(ac.transdate) FROM acc_trans ac
JOIN chart c ON (ac.chart_id = c.id)
$dpt_join
WHERE $where
$dpt_where
$dpt_where_without_arapgl
$project
AND c.accno = ?) AS last_transaction
......
JOIN parts p ON (ac.parts_id = p.id)
JOIN ap a ON (ac.trans_id = a.id)
JOIN chart c ON (p.expense_accno_id = c.id)
$dpt_join
WHERE $invwhere
$dpt_where
$project
......
JOIN parts p ON (ac.parts_id = p.id)
JOIN ar a ON (ac.trans_id = a.id)
JOIN chart c ON (p.income_accno_id = c.id)
$dpt_join
WHERE $invwhere
$dpt_where
$project
......
(SELECT SUM(ac.amount)
FROM acc_trans ac
JOIN chart c ON (ac.chart_id = c.id)
$dpt_join
WHERE $saldowhere
$dpt_where
$dpt_where_without_arapgl
$project
AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)) AS saldo,
(SELECT SUM(ac.amount)
FROM acc_trans ac
JOIN chart c ON (ac.chart_id = c.id)
$dpt_join
WHERE $sumwhere
$dpt_where
$dpt_where_without_arapgl
$project
AND amount > 0
AND c.accno = ?) AS sum_credit,
......
(SELECT SUM(ac.amount)
FROM acc_trans ac
JOIN chart c ON (ac.chart_id = c.id)
$dpt_join
WHERE $sumwhere
$dpt_where
$dpt_where_without_arapgl
$project
AND amount < 0
AND c.accno = ?) AS sum_debit,
......
(SELECT max(ac.transdate) FROM acc_trans ac
JOIN chart c ON (ac.chart_id = c.id)
$dpt_join
WHERE $where
$dpt_where
$dpt_where_without_arapgl
$project
AND c.accno = ?) AS last_transaction
|;
......
}
my ($query, $sth);
my $dpt_join;
my $where;
if ($form->{department_id}) {
$dpt_join = qq| JOIN dpt_trans t ON (t.trans_id = ac.trans_id) |;
$where = qq| AND (t.department_id = | . conv_i($form->{department_id}, 'NULL') . qq|) |;
$where = qq| AND (a.department_id = | . conv_i($form->{department_id}, 'NULL') . qq|) |;
}
if ($form->{fromdate}) {
......
if ($form->{reference}) {
$reference = $dbh->quote('%' . $form->{reference} . '%');
$invnumber = " AND (a.invnumber LIKE $reference)";
$reference = " AND (g.reference LIKE $reference)";
$reference = " AND (a.reference LIKE $reference)";
}
if ($form->{source}) {
$where .= " AND (ac.source ILIKE " . $dbh->quote('%' . $form->{source} . '%') . ") ";
......
'memo' => [ qw(lower_memo) ],
);
my %lowered_columns = (
'invnumber' => { 'gl' => 'g.reference', 'arap' => 'a.invnumber', },
'invnumber' => { 'gl' => 'a.reference', 'arap' => 'a.invnumber', },
'memo' => { 'gl' => 'ac.memo', 'arap' => 'ac.memo', },
'source' => { 'gl' => 'ac.source', 'arap' => 'ac.source', },
'name' => { 'gl' => 'g.description', 'arap' => 'c.name', },
'name' => { 'gl' => 'a.description', 'arap' => 'c.name', },
);
my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
......
FROM acc_trans ac
JOIN $arap a ON (ac.trans_id = a.id)
JOIN $table c ON (c.id = a.${table}_id)
$dpt_join
WHERE (ac.chart_id = ?)
$where
$invnumber
UNION
SELECT g.description, g.reference, NULL AS ordnumber,
SELECT a.description, a.reference, NULL AS ordnumber,
ac.transdate, ac.amount * $ml AS paid, ac.source,
'0' as invoice, g.id, ac.memo, 'gl' AS module
'0' as invoice, a.id, ac.memo, 'gl' AS module
$columns_for_sorting{gl}
FROM acc_trans ac
JOIN gl g ON (g.id = ac.trans_id)
$dpt_join
JOIN gl a ON (a.id = ac.trans_id)
WHERE (ac.chart_id = ?)
$where
$reference
sql/Pg-upgrade2/drop_dpt_trans.sql
-- @tag: drop_dpt_trans
-- @description: Löscht nicht mehr benötigte Tabelle dpt_trans
-- @depends: release_3_0_0
-- @charset: utf-8
-- Drop table dpt_trans:
DROP TABLE dpt_trans;
-- Drop all Trigger which manage dpt_trans:
DROP TRIGGER check_department ON ar;
DROP TRIGGER check_department ON ap;
DROP TRIGGER check_department ON gl;
DROP TRIGGER check_department ON oe;
DROP TRIGGER del_department ON ar;
DROP TRIGGER del_department ON ap;
DROP TRIGGER del_department ON gl;
DROP TRIGGER del_department ON oe;
-- Drop all functions where dpt_trans is used:
DROP FUNCTION check_department();
DROP FUNCTION del_department();

Auch abrufbar als: Unified diff