Revision d05eb259
Von Moritz Bunkus vor fast 18 Jahren hinzugefügt
| SL/AM.pm | ||
|---|---|---|
| 38 | 38 |
package AM; |
| 39 | 39 |
|
| 40 | 40 |
use Data::Dumper; |
| 41 |
use SL::DBUtils; |
|
| 41 | 42 |
|
| 42 | 43 |
sub get_account {
|
| 43 | 44 |
$main::lxdebug->enter_sub(); |
| ... | ... | |
| 83 | 84 |
FROM tax ORDER BY taxkey?; |
| 84 | 85 |
$sth = $dbh->prepare($query); |
| 85 | 86 |
$sth->execute || $form->dberror($query); |
| 86 |
|
|
| 87 |
|
|
| 87 | 88 |
$form->{TAXKEY} = [];
|
| 88 |
|
|
| 89 |
|
|
| 89 | 90 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 90 | 91 |
push @{ $form->{TAXKEY} }, $ref;
|
| 91 | 92 |
} |
| ... | ... | |
| 94 | 95 |
if ($form->{id}) {
|
| 95 | 96 |
|
| 96 | 97 |
$where = " WHERE link='$form->{link}'";
|
| 97 |
|
|
| 98 |
|
|
| 98 |
|
|
| 99 |
|
|
| 99 | 100 |
# get new accounts |
| 100 | 101 |
$query = qq|SELECT id, accno,description |
| 101 | 102 |
FROM chart $where|; |
| 102 | 103 |
$sth = $dbh->prepare($query); |
| 103 | 104 |
$sth->execute || $form->dberror($query); |
| 104 |
|
|
| 105 |
|
|
| 105 | 106 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 106 | 107 |
push @{ $form->{NEWACCOUNT} }, $ref;
|
| 107 | 108 |
} |
| 108 |
|
|
| 109 |
|
|
| 109 | 110 |
$sth->finish; |
| 110 | 111 |
} |
| 111 | 112 |
# check if we have any transactions |
| ... | ... | |
| 216 | 217 |
|
| 217 | 218 |
#Save Taxes |
| 218 | 219 |
if (!$form->{id}) {
|
| 219 |
$query = qq|INSERT INTO taxkeys (chart_id,tax_id,taxkey_id, pos_ustva, startdate) VALUES ((SELECT id FROM chart where accno='$form->{accno}'), $tax_id, $taxkey,$form->{pos_ustva}, $startdate)|;
|
|
| 220 |
$query = qq|INSERT INTO taxkeys (chart_id,tax_id,taxkey_id, pos_ustva, startdate) VALUES ((SELECT id FROM chart where accno='$form->{accno}'), $tax_id, $taxkey,$form->{pos_ustva}, $startdate)|;
|
|
| 220 | 221 |
$dbh->do($query) || $form->dberror($query); |
| 221 | 222 |
} else {
|
| 222 | 223 |
$query = qq|DELETE FROM taxkeys WHERE chart_id=$form->{id} AND tax_id=$tax_id|;
|
| ... | ... | |
| 226 | 227 |
} |
| 227 | 228 |
|
| 228 | 229 |
# if ($form->{IC_taxpart} || $form->{IC_taxservice} || $form->{CT_tax}) {
|
| 229 |
#
|
|
| 230 |
# |
|
| 230 | 231 |
# my $chart_id = $form->{id};
|
| 231 |
#
|
|
| 232 |
# |
|
| 232 | 233 |
# unless ($form->{id}) {
|
| 233 |
#
|
|
| 234 |
# |
|
| 234 | 235 |
# # get id from chart |
| 235 | 236 |
# $query = qq|SELECT c.id |
| 236 | 237 |
# FROM chart c |
| 237 | 238 |
# WHERE c.accno = '$form->{accno}'|;
|
| 238 | 239 |
# $sth = $dbh->prepare($query); |
| 239 | 240 |
# $sth->execute || $form->dberror($query); |
| 240 |
#
|
|
| 241 |
# |
|
| 241 | 242 |
# ($chart_id) = $sth->fetchrow_array; |
| 242 | 243 |
# $sth->finish; |
| 243 | 244 |
# } |
| 244 |
#
|
|
| 245 |
# |
|
| 245 | 246 |
# # add account if it doesn't exist in tax |
| 246 | 247 |
# $query = qq|SELECT t.chart_id |
| 247 | 248 |
# FROM tax t |
| 248 | 249 |
# WHERE t.chart_id = $chart_id|; |
| 249 | 250 |
# $sth = $dbh->prepare($query); |
| 250 | 251 |
# $sth->execute || $form->dberror($query); |
| 251 |
#
|
|
| 252 |
# |
|
| 252 | 253 |
# my ($tax_id) = $sth->fetchrow_array; |
| 253 | 254 |
# $sth->finish; |
| 254 |
#
|
|
| 255 |
# |
|
| 255 | 256 |
# # add tax if it doesn't exist |
| 256 | 257 |
# unless ($tax_id) {
|
| 257 | 258 |
# $query = qq|INSERT INTO tax (chart_id, rate) |
| ... | ... | |
| 259 | 260 |
# $dbh->do($query) || $form->dberror($query); |
| 260 | 261 |
# } |
| 261 | 262 |
# } else {
|
| 262 |
#
|
|
| 263 |
# |
|
| 263 | 264 |
# # remove tax |
| 264 | 265 |
# if ($form->{id}) {
|
| 265 | 266 |
# $query = qq|DELETE FROM tax |
| ... | ... | |
| 1023 | 1024 |
|
| 1024 | 1025 |
if ($form->{id}) {
|
| 1025 | 1026 |
my $query = |
| 1026 |
qq|SELECT description, inventory_accno_id, (select accno from chart where id=inventory_accno_id) as inventory_accno, income_accno_id_0, (select accno from chart where id=income_accno_id_0) as income_accno_0, expense_accno_id_0, (select accno from chart where id=expense_accno_id_0) as expense_accno_0, income_accno_id_1, (select accno from chart where id=income_accno_id_1) as income_accno_1, expense_accno_id_1, (select accno from chart where id=expense_accno_id_1) as expense_accno_1, income_accno_id_2, (select accno from chart where id=income_accno_id_2) as income_accno_2, expense_accno_id_2, (select accno from chart where id=expense_accno_id_2) as expense_accno_2, income_accno_id_3, (select accno from chart where id=income_accno_id_3) as income_accno_3, expense_accno_id_3, (select accno from chart where id=expense_accno_id_3) as expense_accno_3 |
|
| 1027 |
FROM buchungsgruppen |
|
| 1028 |
WHERE id = $form->{id}|;
|
|
| 1027 |
qq|SELECT description, inventory_accno_id, |
|
| 1028 |
(SELECT accno FROM chart WHERE id = inventory_accno_id) AS inventory_accno, |
|
| 1029 |
income_accno_id_0, |
|
| 1030 |
(SELECT accno FROM chart WHERE id = income_accno_id_0) AS income_accno_0, |
|
| 1031 |
expense_accno_id_0, |
|
| 1032 |
(SELECT accno FROM chart WHERE id = expense_accno_id_0) AS expense_accno_0, |
|
| 1033 |
income_accno_id_1, |
|
| 1034 |
(SELECT accno FROM chart WHERE id = income_accno_id_1) AS income_accno_1, |
|
| 1035 |
expense_accno_id_1, |
|
| 1036 |
(SELECT accno FROM chart WHERE id = expense_accno_id_1) AS expense_accno_1, |
|
| 1037 |
income_accno_id_2, |
|
| 1038 |
(SELECT accno FROM chart WHERE id = income_accno_id_2) AS income_accno_2, |
|
| 1039 |
expense_accno_id_2, |
|
| 1040 |
(select accno FROM chart WHERE id = expense_accno_id_2) AS expense_accno_2, |
|
| 1041 |
income_accno_id_3, |
|
| 1042 |
(SELECT accno FROM chart WHERE id = income_accno_id_3) AS income_accno_3, |
|
| 1043 |
expense_accno_id_3, |
|
| 1044 |
(SELECT accno FROM chart WHERE id = expense_accno_id_3) AS expense_accno_3 |
|
| 1045 |
FROM buchungsgruppen |
|
| 1046 |
WHERE id = ?|; |
|
| 1029 | 1047 |
my $sth = $dbh->prepare($query); |
| 1030 |
$sth->execute || $form->dberror($query);
|
|
| 1031 |
|
|
| 1048 |
$sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
|
|
| 1049 |
|
|
| 1032 | 1050 |
my $ref = $sth->fetchrow_hashref(NAME_lc); |
| 1033 |
|
|
| 1051 |
|
|
| 1034 | 1052 |
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
| 1035 |
|
|
| 1036 |
$sth->finish; |
|
| 1037 |
|
|
| 1038 |
my $query = |
|
| 1039 |
qq|SELECT count(id) as anzahl |
|
| 1040 |
FROM parts |
|
| 1041 |
WHERE buchungsgruppen_id = $form->{id}|;
|
|
| 1042 |
my $sth = $dbh->prepare($query); |
|
| 1043 |
$sth->execute || $form->dberror($query); |
|
| 1044 |
|
|
| 1045 |
my $ref = $sth->fetchrow_hashref(NAME_lc); |
|
| 1046 |
if (!$ref->{anzahl}) {
|
|
| 1047 |
$form->{orphaned} = 1;
|
|
| 1048 |
} |
|
| 1053 |
|
|
| 1049 | 1054 |
$sth->finish; |
| 1050 | 1055 |
|
| 1056 |
my $query = |
|
| 1057 |
qq|SELECT count(id) = 0 AS orphaned |
|
| 1058 |
FROM parts |
|
| 1059 |
WHERE buchungsgruppen_id = ?|; |
|
| 1060 |
($form->{orphaned}) = $dbh->selectrow_array($query, undef, $form->{id});
|
|
| 1061 |
$form->dberror($query . " ($form->{id})") if ($dbh->err);
|
|
| 1051 | 1062 |
} |
| 1052 | 1063 |
|
| 1053 | 1064 |
$query = "SELECT inventory_accno_id, income_accno_id, expense_accno_id ". |
| ... | ... | |
| 1105 | 1116 |
# connect to database |
| 1106 | 1117 |
my $dbh = $form->dbconnect($myconfig); |
| 1107 | 1118 |
|
| 1108 |
$form->{description} =~ s/\'/\'\'/g;
|
|
| 1109 |
|
|
| 1119 |
my @values = ($form->{description}, $form->{inventory_accno_id},
|
|
| 1120 |
$form->{income_accno_id_0}, $form->{expense_accno_id_0},
|
|
| 1121 |
$form->{income_accno_id_1}, $form->{expense_accno_id_1},
|
|
| 1122 |
$form->{income_accno_id_2}, $form->{expense_accno_id_2},
|
|
| 1123 |
$form->{income_accno_id_3}, $form->{expense_accno_id_3});
|
|
| 1110 | 1124 |
|
| 1111 | 1125 |
# id is the old record |
| 1112 | 1126 |
if ($form->{id}) {
|
| 1113 | 1127 |
$query = qq|UPDATE buchungsgruppen SET |
| 1114 |
description = '$form->{description}',
|
|
| 1115 |
inventory_accno_id = '$form->{inventory_accno_id}',
|
|
| 1116 |
income_accno_id_0 = '$form->{income_accno_id_0}',
|
|
| 1117 |
expense_accno_id_0 = '$form->{expense_accno_id_0}',
|
|
| 1118 |
income_accno_id_1 = '$form->{income_accno_id_1}',
|
|
| 1119 |
expense_accno_id_1 = '$form->{expense_accno_id_1}',
|
|
| 1120 |
income_accno_id_2 = '$form->{income_accno_id_2}',
|
|
| 1121 |
expense_accno_id_2 = '$form->{expense_accno_id_2}',
|
|
| 1122 |
income_accno_id_3 = '$form->{income_accno_id_3}',
|
|
| 1123 |
expense_accno_id_3 = '$form->{expense_accno_id_3}'
|
|
| 1124 |
WHERE id = $form->{id}|;
|
|
| 1128 |
description = ?, inventory_accno_id = ?, |
|
| 1129 |
income_accno_id_0 = ?, expense_accno_id_0 = ?, |
|
| 1130 |
income_accno_id_1 = ?, expense_accno_id_1 = ?, |
|
| 1131 |
income_accno_id_2 = ?, expense_accno_id_2 = ?, |
|
| 1132 |
income_accno_id_3 = ?, expense_accno_id_3 = ? |
|
| 1133 |
WHERE id = ?|; |
|
| 1134 |
push(@values, $form->{id});
|
|
| 1125 | 1135 |
} else {
|
| 1126 | 1136 |
$query = qq|INSERT INTO buchungsgruppen |
| 1127 |
(description, inventory_accno_id, income_accno_id_0, expense_accno_id_0, income_accno_id_1, expense_accno_id_1, income_accno_id_2, expense_accno_id_2, income_accno_id_3, expense_accno_id_3) |
|
| 1128 |
VALUES ('$form->{description}', '$form->{inventory_accno_id}', '$form->{income_accno_id_0}', '$form->{expense_accno_id_0}', '$form->{income_accno_id_1}', '$form->{expense_accno_id_1}', '$form->{income_accno_id_2}', '$form->{expense_accno_id_2}', '$form->{income_accno_id_3}', '$form->{expense_accno_id_3}')|;
|
|
| 1137 |
(description, inventory_accno_id, |
|
| 1138 |
income_accno_id_0, expense_accno_id_0, |
|
| 1139 |
income_accno_id_1, expense_accno_id_1, |
|
| 1140 |
income_accno_id_2, expense_accno_id_2, |
|
| 1141 |
income_accno_id_3, expense_accno_id_3) |
|
| 1142 |
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; |
|
| 1129 | 1143 |
} |
| 1130 |
$dbh->do($query) || $form->dberror($query);
|
|
| 1144 |
do_query($form, $dbh, $query, @values);
|
|
| 1131 | 1145 |
|
| 1132 | 1146 |
$dbh->disconnect; |
| 1133 | 1147 |
|
| ... | ... | |
| 1142 | 1156 |
# connect to database |
| 1143 | 1157 |
my $dbh = $form->dbconnect($myconfig); |
| 1144 | 1158 |
|
| 1145 |
$query = qq|DELETE FROM buchungsgruppen |
|
| 1146 |
WHERE id = $form->{id}|;
|
|
| 1147 |
$dbh->do($query) || $form->dberror($query); |
|
| 1159 |
$query = qq|DELETE FROM buchungsgruppen WHERE id = ?|; |
|
| 1160 |
do_query($form, $dbh, $query, $form->{id});
|
|
| 1148 | 1161 |
|
| 1149 | 1162 |
$dbh->disconnect; |
| 1150 | 1163 |
|
| ... | ... | |
| 1267 | 1280 |
$sth = $dbh->prepare($query); |
| 1268 | 1281 |
$sth->execute || $form->dberror($query); |
| 1269 | 1282 |
|
| 1270 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 1283 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 1271 | 1284 |
$ref->{percent_skonto} = $form->format_amount($myconfig,($ref->{percent_skonto} * 100));
|
| 1272 | 1285 |
push @{ $form->{ALL} }, $ref;
|
| 1273 | 1286 |
} |
Auch abrufbar als: Unified diff
Kosmetik: Leerzeichen an Zeilenenden entfernt. Die Abfragen für Buchungsgruppen umformatiert, damit sie besser lesbar sind. Kampf den Tabs!