X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FAM.pm;h=a2e201f61149afe801583e0ee474346c51b50dab;hb=baf92f533975d1224700a85d0b8ededd8246d09b;hp=62406e74c24a2a7786089bb98913555dd76ea395;hpb=d319704a66e9be64da837ccea10af6774c2b0838;p=kivitendo-erp.git diff --git a/SL/AM.pm b/SL/AM.pm index 62406e74c..a2e201f61 100644 --- a/SL/AM.pm +++ b/SL/AM.pm @@ -37,6 +37,8 @@ package AM; +use Data::Dumper; + sub get_account { $main::lxdebug->enter_sub(); @@ -48,7 +50,7 @@ sub get_account { my $dbh = $form->dbconnect($myconfig); my $query = qq|SELECT c.accno, c.description, c.charttype, c.gifi_accno, - c.category, c.link, c.taxkey_id, c.pos_ustva, c.pos_bwa, c.pos_bilanz,c.pos_eur + c.category, c.link, c.taxkey_id, c.pos_ustva, c.pos_bwa, c.pos_bilanz,c.pos_eur, c.new_chart_id, c.valid_from FROM chart c WHERE c.id = $form->{id}|; @@ -76,7 +78,7 @@ sub get_account { $sth->finish; # get taxkeys and description - $query = qq|SELECT taxkey, taxdescription + $query = qq|SELECT taxkey, taxdescription FROM tax|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); @@ -88,7 +90,23 @@ sub get_account { } $sth->finish; + if ($form->{id}) { + $where = " WHERE link='$form->{link}'"; + + + # get new accounts + $query = qq|SELECT id, accno,description + FROM chart $where|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{NEWACCOUNT} }, $ref; + } + + $sth->finish; + } # check if we have any transactions $query = qq|SELECT a.trans_id FROM acc_trans a WHERE a.chart_id = $form->{id}|; @@ -99,6 +117,21 @@ sub get_account { $form->{orphaned} = !$form->{orphaned}; $sth->finish; + # check if new account is active + $form->{new_chart_valid} = 0; + if ($form->{new_chart_id}) { + $query = qq|SELECT current_date-valid_from FROM chart + WHERE id = $form->{id}|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my ($count) = $sth->fetchrow_array; + if ($count >=0) { + $form->{new_chart_valid} = 1; + } + $sth->finish; + } + $dbh->disconnect; $main::lxdebug->leave_sub(); @@ -145,9 +178,11 @@ sub save_account { } map({ $form->{$_} = "NULL" unless ($form->{$_}); } - qw(pos_ustva pos_bwa pos_bilanz pos_eur)); + qw(pos_ustva pos_bwa pos_bilanz pos_eur new_chart_id)); - if ($form->{id}) { + $form->{valid_from} = ($form->{valid_from}) ? "'$form->{valid_from}'" : "NULL"; + + if ($form->{id} && $form->{orphaned}) { $query = qq|UPDATE chart SET accno = '$form->{accno}', description = '$form->{description}', @@ -159,15 +194,22 @@ sub save_account { pos_ustva = $form->{pos_ustva}, pos_bwa = $form->{pos_bwa}, pos_bilanz = $form->{pos_bilanz}, - pos_eur = $form->{pos_eur} + pos_eur = $form->{pos_eur}, + new_chart_id = $form->{new_chart_id}, + valid_from = $form->{valid_from} + WHERE id = $form->{id}|; + } elsif ($form->{id} && !$form->{new_chart_valid}) { + $query = qq|UPDATE chart SET + new_chart_id = $form->{new_chart_id}, + valid_from = $form->{valid_from} WHERE id = $form->{id}|; } else { - $query = qq|INSERT INTO chart - (accno, description, charttype, gifi_accno, category, link, taxkey_id, pos_ustva, pos_bwa, pos_bilanz,pos_eur) + $query = qq|INSERT INTO chart + (accno, description, charttype, gifi_accno, category, link, taxkey_id, pos_ustva, pos_bwa, pos_bilanz,pos_eur, new_chart_id, valid_from) VALUES ('$form->{accno}', '$form->{description}', '$form->{charttype}', '$form->{gifi_accno}', - '$form->{category}', '$form->{link}', $form->{taxkey_id}, $form->{pos_ustva}, $form->{pos_bwa}, $form->{pos_bilanz}, $form->{pos_eur})|; + '$form->{category}', '$form->{link}', $form->{taxkey_id}, $form->{pos_ustva}, $form->{pos_bwa}, $form->{pos_bilanz}, $form->{pos_eur}, $form->{new_chart_id}, $form->{valid_from})|; } $dbh->do($query) || $form->dberror($query); @@ -251,7 +293,7 @@ sub delete_account { # set inventory_accno_id, income_accno_id, expense_accno_id to defaults $query = qq|UPDATE parts - SET inventory_accno_id = + SET inventory_accno_id = (SELECT inventory_accno_id FROM defaults) WHERE inventory_accno_id = $form->{id}|; $dbh->do($query) || $form->dberror($query); @@ -274,16 +316,628 @@ sub delete_account { $dbh->do($query) || $form->dberror($query); } - # commit and redirect - my $rc = $dbh->commit; + # commit and redirect + my $rc = $dbh->commit; + $dbh->disconnect; + + $main::lxdebug->leave_sub(); + + return $rc; +} + +sub gifi_accounts { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT accno, description + FROM gifi + ORDER BY accno|; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{ALL} }, $ref; + } + + $sth->finish; + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub get_gifi { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT g.accno, g.description + FROM gifi g + WHERE g.accno = '$form->{accno}'|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $ref = $sth->fetchrow_hashref(NAME_lc); + + map { $form->{$_} = $ref->{$_} } keys %$ref; + + $sth->finish; + + # check for transactions + $query = qq|SELECT count(*) FROM acc_trans a, chart c, gifi g + WHERE c.gifi_accno = g.accno + AND a.chart_id = c.id + AND g.accno = '$form->{accno}'|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + ($form->{orphaned}) = $sth->fetchrow_array; + $sth->finish; + $form->{orphaned} = !$form->{orphaned}; + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub save_gifi { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->{description} =~ s/\'/\'\'/g; + + # id is the old account number! + if ($form->{id}) { + $query = qq|UPDATE gifi SET + accno = '$form->{accno}', + description = '$form->{description}' + WHERE accno = '$form->{id}'|; + } else { + $query = qq|INSERT INTO gifi + (accno, description) + VALUES ('$form->{accno}', '$form->{description}')|; + } + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub delete_gifi { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + # id is the old account number! + $query = qq|DELETE FROM gifi + WHERE accno = '$form->{id}'|; + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub warehouses { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT id, description + FROM warehouse + ORDER BY 2|; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{ALL} }, $ref; + } + + $sth->finish; + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub get_warehouse { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT w.description + FROM warehouse w + WHERE w.id = $form->{id}|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $ref = $sth->fetchrow_hashref(NAME_lc); + + map { $form->{$_} = $ref->{$_} } keys %$ref; + + $sth->finish; + + # see if it is in use + $query = qq|SELECT count(*) FROM inventory i + WHERE i.warehouse_id = $form->{id}|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + ($form->{orphaned}) = $sth->fetchrow_array; + $form->{orphaned} = !$form->{orphaned}; + $sth->finish; + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub save_warehouse { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->{description} =~ s/\'/\'\'/g; + + if ($form->{id}) { + $query = qq|UPDATE warehouse SET + description = '$form->{description}' + WHERE id = $form->{id}|; + } else { + $query = qq|INSERT INTO warehouse + (description) + VALUES ('$form->{description}')|; + } + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub delete_warehouse { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $query = qq|DELETE FROM warehouse + WHERE id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub departments { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT d.id, d.description, d.role + FROM department d + ORDER BY 2|; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{ALL} }, $ref; + } + + $sth->finish; + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub get_department { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT d.description, d.role + FROM department d + WHERE d.id = $form->{id}|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $ref = $sth->fetchrow_hashref(NAME_lc); + + map { $form->{$_} = $ref->{$_} } keys %$ref; + + $sth->finish; + + # see if it is in use + $query = qq|SELECT count(*) FROM dpt_trans d + WHERE d.department_id = $form->{id}|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + ($form->{orphaned}) = $sth->fetchrow_array; + $form->{orphaned} = !$form->{orphaned}; + $sth->finish; + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub save_department { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->{description} =~ s/\'/\'\'/g; + + if ($form->{id}) { + $query = qq|UPDATE department SET + description = '$form->{description}', + role = '$form->{role}' + WHERE id = $form->{id}|; + } else { + $query = qq|INSERT INTO department + (description, role) + VALUES ('$form->{description}', '$form->{role}')|; + } + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub delete_department { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $query = qq|DELETE FROM department + WHERE id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub lead { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT id, lead + FROM leads + ORDER BY 2|; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{ALL} }, $ref; + } + + $sth->finish; + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub get_lead { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = + qq|SELECT l.id, l.lead + FROM leads l + WHERE l.id = $form->{id}|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $ref = $sth->fetchrow_hashref(NAME_lc); + + map { $form->{$_} = $ref->{$_} } keys %$ref; + + $sth->finish; + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub save_lead { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->{lead} =~ s/\'/\'\'/g; + + # id is the old record + if ($form->{id}) { + $query = qq|UPDATE leads SET + lead = '$form->{description}' + WHERE id = $form->{id}|; + } else { + $query = qq|INSERT INTO leads + (lead) + VALUES ('$form->{description}')|; + } + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub delete_lead { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $query = qq|DELETE FROM leads + WHERE id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub business { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT id, description, discount, customernumberinit, salesman + FROM business + ORDER BY 2|; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{ALL} }, $ref; + } + + $sth->finish; + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub get_business { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = + qq|SELECT b.description, b.discount, b.customernumberinit, b.salesman + FROM business b + WHERE b.id = $form->{id}|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $ref = $sth->fetchrow_hashref(NAME_lc); + + map { $form->{$_} = $ref->{$_} } keys %$ref; + + $sth->finish; + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub save_business { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->{description} =~ s/\'/\'\'/g; + $form->{discount} /= 100; + $form->{salesman} *= 1; + + # id is the old record + if ($form->{id}) { + $query = qq|UPDATE business SET + description = '$form->{description}', + discount = $form->{discount}, + customernumberinit = '$form->{customernumberinit}', + salesman = '$form->{salesman}' + WHERE id = $form->{id}|; + } else { + $query = qq|INSERT INTO business + (description, discount, customernumberinit, salesman) + VALUES ('$form->{description}', $form->{discount}, '$form->{customernumberinit}', '$form->{salesman}')|; + } + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub delete_business { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $query = qq|DELETE FROM business + WHERE id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + + +sub language { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT id, description, template_code, article_code + FROM language + ORDER BY 2|; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{ALL} }, $ref; + } + + $sth->finish; + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub get_language { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = + qq|SELECT l.description, l.template_code, l.article_code + FROM language l + WHERE l.id = $form->{id}|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $ref = $sth->fetchrow_hashref(NAME_lc); + + map { $form->{$_} = $ref->{$_} } keys %$ref; + + $sth->finish; + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + +sub save_language { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->{description} =~ s/\'/\'\'/g; + $form->{article_code} =~ s/\'/\'\'/g; + $form->{template_code} =~ s/\'/\'\'/g; + + + # id is the old record + if ($form->{id}) { + $query = qq|UPDATE language SET + description = '$form->{description}', + template_code = '$form->{template_code}', + article_code = '$form->{article_code}' + WHERE id = $form->{id}|; + } else { + $query = qq|INSERT INTO language + (description, template_code, article_code) + VALUES ('$form->{description}', '$form->{template_code}', '$form->{article_code}')|; + } + $dbh->do($query) || $form->dberror($query); + $dbh->disconnect; $main::lxdebug->leave_sub(); +} - return $rc; +sub delete_language { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $query = qq|DELETE FROM language + WHERE id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + + $main::lxdebug->leave_sub(); } -sub gifi_accounts { + +sub buchungsgruppe { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -291,9 +945,9 @@ sub gifi_accounts { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT accno, description - FROM gifi - ORDER BY accno|; + my $query = qq|SELECT id, 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 + FROM buchungsgruppen + ORDER BY id|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); @@ -308,7 +962,7 @@ sub gifi_accounts { $main::lxdebug->leave_sub(); } -sub get_gifi { +sub get_buchungsgruppe { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -316,36 +970,74 @@ sub get_gifi { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT g.accno, g.description - FROM gifi g - WHERE g.accno = '$form->{accno}'|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - my $ref = $sth->fetchrow_hashref(NAME_lc); + if ($form->{id}) { + my $query = + 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 + FROM buchungsgruppen + WHERE id = $form->{id}|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $ref = $sth->fetchrow_hashref(NAME_lc); + + map { $form->{$_} = $ref->{$_} } keys %$ref; + + $sth->finish; + + my $query = + qq|SELECT count(id) as anzahl + FROM parts + WHERE buchungsgruppen_id = $form->{id}|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + my $ref = $sth->fetchrow_hashref(NAME_lc); + if (!$ref->{anzahl}) { + $form->{orphaned} = 1; + } + $sth->finish; - map { $form->{$_} = $ref->{$_} } keys %$ref; + } + my $module = "IC"; + $query = qq|SELECT c.accno, c.description, c.link, c.id, + d.inventory_accno_id, d.income_accno_id, d.expense_accno_id + FROM chart c, defaults d + WHERE c.link LIKE '%$module%' + ORDER BY c.accno|; - $sth->finish; - # check for transactions - $query = qq|SELECT count(*) FROM acc_trans a, chart c, gifi g - WHERE c.gifi_accno = g.accno - AND a.chart_id = c.id - AND g.accno = '$form->{accno}'|; - $sth = $dbh->prepare($query); + my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); - - ($form->{orphaned}) = $sth->fetchrow_array; + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + foreach my $key (split /:/, $ref->{link}) { + if ($key =~ /$module/) { + if ( ($ref->{id} eq $ref->{inventory_accno_id}) + || ($ref->{id} eq $ref->{income_accno_id}) + || ($ref->{id} eq $ref->{expense_accno_id})) { + push @{ $form->{"${module}_links"}{$key} }, + { accno => $ref->{accno}, + description => $ref->{description}, + selected => "selected", + id => $ref->{id} }; + } else { + push @{ $form->{"${module}_links"}{$key} }, + { accno => $ref->{accno}, + description => $ref->{description}, + selected => "", + id => $ref->{id} }; + } + } + } + } $sth->finish; - $form->{orphaned} = !$form->{orphaned}; + $dbh->disconnect; $main::lxdebug->leave_sub(); } -sub save_gifi { +sub save_buchungsgruppe { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -355,16 +1047,25 @@ sub save_gifi { $form->{description} =~ s/\'/\'\'/g; - # id is the old account number! + + # id is the old record if ($form->{id}) { - $query = qq|UPDATE gifi SET - accno = '$form->{accno}', - description = '$form->{description}' - WHERE accno = '$form->{id}'|; + $query = qq|UPDATE buchungsgruppen SET + description = '$form->{description}', + inventory_accno_id = '$form->{inventory_accno_id}', + income_accno_id_0 = '$form->{income_accno_id_0}', + expense_accno_id_0 = '$form->{expense_accno_id_0}', + income_accno_id_1 = '$form->{income_accno_id_1}', + expense_accno_id_1 = '$form->{expense_accno_id_1}', + income_accno_id_2 = '$form->{income_accno_id_2}', + expense_accno_id_2 = '$form->{expense_accno_id_2}', + income_accno_id_3 = '$form->{income_accno_id_3}', + expense_accno_id_3 = '$form->{expense_accno_id_3}' + WHERE id = $form->{id}|; } else { - $query = qq|INSERT INTO gifi - (accno, description) - VALUES ('$form->{accno}', '$form->{description}')|; + $query = qq|INSERT INTO buchungsgruppen + (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) + 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}')|; } $dbh->do($query) || $form->dberror($query); @@ -373,7 +1074,7 @@ sub save_gifi { $main::lxdebug->leave_sub(); } -sub delete_gifi { +sub delete_buchungsgruppe { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -381,9 +1082,8 @@ sub delete_gifi { # connect to database my $dbh = $form->dbconnect($myconfig); - # id is the old account number! - $query = qq|DELETE FROM gifi - WHERE accno = '$form->{id}'|; + $query = qq|DELETE FROM buchungsgruppe + WHERE id = $form->{id}|; $dbh->do($query) || $form->dberror($query); $dbh->disconnect; @@ -391,7 +1091,7 @@ sub delete_gifi { $main::lxdebug->leave_sub(); } -sub warehouses { +sub printer { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -399,8 +1099,8 @@ sub warehouses { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT id, description - FROM warehouse + my $query = qq|SELECT id, printer_description, template_code, printer_command + FROM printers ORDER BY 2|; $sth = $dbh->prepare($query); @@ -416,7 +1116,7 @@ sub warehouses { $main::lxdebug->leave_sub(); } -sub get_warehouse { +sub get_printer { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -424,9 +1124,10 @@ sub get_warehouse { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT w.description - FROM warehouse w - WHERE w.id = $form->{id}|; + my $query = + qq|SELECT p.printer_description, p.template_code, p.printer_command + FROM printers p + WHERE p.id = $form->{id}|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); @@ -436,22 +1137,12 @@ sub get_warehouse { $sth->finish; - # see if it is in use - $query = qq|SELECT count(*) FROM inventory i - WHERE i.warehouse_id = $form->{id}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - ($form->{orphaned}) = $sth->fetchrow_array; - $form->{orphaned} = !$form->{orphaned}; - $sth->finish; - $dbh->disconnect; $main::lxdebug->leave_sub(); } -sub save_warehouse { +sub save_printer { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -459,16 +1150,22 @@ sub save_warehouse { # connect to database my $dbh = $form->dbconnect($myconfig); - $form->{description} =~ s/\'/\'\'/g; + $form->{printer_description} =~ s/\'/\'\'/g; + $form->{printer_command} =~ s/\'/\'\'/g; + $form->{template_code} =~ s/\'/\'\'/g; + + # id is the old record if ($form->{id}) { - $query = qq|UPDATE warehouse SET - description = '$form->{description}' + $query = qq|UPDATE printers SET + printer_description = '$form->{printer_description}', + template_code = '$form->{template_code}', + printer_command = '$form->{printer_command}' WHERE id = $form->{id}|; } else { - $query = qq|INSERT INTO warehouse - (description) - VALUES ('$form->{description}')|; + $query = qq|INSERT INTO printers + (printer_description, template_code, printer_command) + VALUES ('$form->{printer_description}', '$form->{template_code}', '$form->{printer_command}')|; } $dbh->do($query) || $form->dberror($query); @@ -477,7 +1174,7 @@ sub save_warehouse { $main::lxdebug->leave_sub(); } -sub delete_warehouse { +sub delete_printer { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -485,7 +1182,7 @@ sub delete_warehouse { # connect to database my $dbh = $form->dbconnect($myconfig); - $query = qq|DELETE FROM warehouse + $query = qq|DELETE FROM printers WHERE id = $form->{id}|; $dbh->do($query) || $form->dberror($query); @@ -494,7 +1191,7 @@ sub delete_warehouse { $main::lxdebug->leave_sub(); } -sub departments { +sub adr { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -502,9 +1199,9 @@ sub departments { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT d.id, d.description, d.role - FROM department d - ORDER BY 2|; + my $query = qq|SELECT id, adr_description, adr_code + FROM adr + ORDER BY adr_code|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); @@ -519,7 +1216,7 @@ sub departments { $main::lxdebug->leave_sub(); } -sub get_department { +sub get_adr { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -527,9 +1224,10 @@ sub get_department { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT d.description, d.role - FROM department d - WHERE d.id = $form->{id}|; + my $query = + qq|SELECT a.adr_description, a.adr_code + FROM adr a + WHERE a.id = $form->{id}|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); @@ -539,22 +1237,12 @@ sub get_department { $sth->finish; - # see if it is in use - $query = qq|SELECT count(*) FROM dpt_trans d - WHERE d.department_id = $form->{id}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - ($form->{orphaned}) = $sth->fetchrow_array; - $form->{orphaned} = !$form->{orphaned}; - $sth->finish; - $dbh->disconnect; $main::lxdebug->leave_sub(); } -sub save_department { +sub save_adr { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -562,17 +1250,20 @@ sub save_department { # connect to database my $dbh = $form->dbconnect($myconfig); - $form->{description} =~ s/\'/\'\'/g; + $form->{adr_description} =~ s/\'/\'\'/g; + $form->{adr_code} =~ s/\'/\'\'/g; + + # id is the old record if ($form->{id}) { - $query = qq|UPDATE department SET - description = '$form->{description}', - role = '$form->{role}' + $query = qq|UPDATE adr SET + adr_description = '$form->{adr_description}', + adr_code = '$form->{adr_code}' WHERE id = $form->{id}|; } else { - $query = qq|INSERT INTO department - (description, role) - VALUES ('$form->{description}', '$form->{role}')|; + $query = qq|INSERT INTO adr + (adr_description, adr_code) + VALUES ('$form->{adr_description}', '$form->{adr_code}')|; } $dbh->do($query) || $form->dberror($query); @@ -581,7 +1272,7 @@ sub save_department { $main::lxdebug->leave_sub(); } -sub delete_department { +sub delete_adr { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -589,7 +1280,7 @@ sub delete_department { # connect to database my $dbh = $form->dbconnect($myconfig); - $query = qq|DELETE FROM department + $query = qq|DELETE FROM adr WHERE id = $form->{id}|; $dbh->do($query) || $form->dberror($query); @@ -598,7 +1289,7 @@ sub delete_department { $main::lxdebug->leave_sub(); } -sub business { +sub payment { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -606,14 +1297,15 @@ sub business { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT id, description, discount, customernumberinit, salesman - FROM business - ORDER BY 2|; + my $query = qq|SELECT * + FROM payment_terms + ORDER BY id|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + $ref->{percent_skonto} = $form->format_amount($myconfig,($ref->{percent_skonto} * 100)); push @{ $form->{ALL} }, $ref; } @@ -623,7 +1315,7 @@ sub business { $main::lxdebug->leave_sub(); } -sub get_business { +sub get_payment { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -632,13 +1324,14 @@ sub get_business { my $dbh = $form->dbconnect($myconfig); my $query = - qq|SELECT b.description, b.discount, b.customernumberinit, b.salesman - FROM business b - WHERE b.id = $form->{id}|; + qq|SELECT * + FROM payment_terms + WHERE id = $form->{id}|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); my $ref = $sth->fetchrow_hashref(NAME_lc); + $ref->{percent_skonto} = $form->format_amount($myconfig,($ref->{percent_skonto} * 100)); map { $form->{$_} = $ref->{$_} } keys %$ref; @@ -649,7 +1342,7 @@ sub get_business { $main::lxdebug->leave_sub(); } -sub save_business { +sub save_payment { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -658,21 +1351,29 @@ sub save_business { my $dbh = $form->dbconnect($myconfig); $form->{description} =~ s/\'/\'\'/g; - $form->{discount} /= 100; - $form->{salesman} *= 1; + $form->{description_long} =~ s/\'/\'\'/g; + $percentskonto = $form->parse_amount($myconfig, $form->{percent_skonto}) /100; + $form->{ranking} *= 1; + $form->{terms_netto} *= 1; + $form->{terms_skonto} *= 1; + $form->{percent_skonto} *= 1; + + # id is the old record if ($form->{id}) { - $query = qq|UPDATE business SET + $query = qq|UPDATE payment_terms SET description = '$form->{description}', - discount = $form->{discount}, - customernumberinit = '$form->{customernumberinit}', - salesman = '$form->{salesman}' + ranking = $form->{ranking}, + description_long = '$form->{description_long}', + terms_netto = $form->{terms_netto}, + terms_skonto = $form->{terms_skonto}, + percent_skonto = $percentskonto WHERE id = $form->{id}|; } else { - $query = qq|INSERT INTO business - (description, discount, customernumberinit, salesman) - VALUES ('$form->{description}', $form->{discount}, '$form->{customernumberinit}', '$form->{salesman}')|; + $query = qq|INSERT INTO payment_terms + (description, ranking, description_long, terms_netto, terms_skonto, percent_skonto) + VALUES ('$form->{description}', $form->{ranking}, '$form->{description_long}', $form->{terms_netto}, $form->{terms_skonto}, $percentskonto)|; } $dbh->do($query) || $form->dberror($query); @@ -681,7 +1382,7 @@ sub save_business { $main::lxdebug->leave_sub(); } -sub delete_business { +sub delete_payment { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -689,7 +1390,7 @@ sub delete_business { # connect to database my $dbh = $form->dbconnect($myconfig); - $query = qq|DELETE FROM business + $query = qq|DELETE FROM payment_terms WHERE id = $form->{id}|; $dbh->do($query) || $form->dberror($query); @@ -767,7 +1468,7 @@ sub save_sic { description = '$form->{description}' WHERE code = '$form->{id}'|; } else { - $query = qq|INSERT INTO sic + $query = qq|INSERT INTO sic (code, sictype, description) VALUES ('$form->{code}', '$form->{sictype}', '$form->{description}')|; } @@ -847,7 +1548,7 @@ sub save_preferences { # user specific variables are in myconfig # save defaults my $query = qq|UPDATE defaults SET - inventory_accno_id = + inventory_accno_id = (SELECT c.id FROM chart c WHERE c.accno = '$form->{inventory_accno}'), income_accno_id = @@ -863,6 +1564,7 @@ sub save_preferences { (SELECT c.id FROM chart c WHERE c.accno = '$form->{fxloss_accno}'), invnumber = '$form->{invnumber}', + cnnumber = '$form->{cnnumber}', sonumber = '$form->{sonumber}', ponumber = '$form->{ponumber}', sqnumber = '$form->{sqnumber}', @@ -1355,5 +2057,197 @@ sub closebooks { $main::lxdebug->leave_sub(); } -1; +sub get_base_unit { + my ($self, $units, $unit_name, $factor) = @_; + + $factor = 1 unless ($factor); + + my $unit = $units->{$unit_name}; + + if (!defined($unit) || !$unit->{"base_unit"} || + ($unit_name eq $unit->{"base_unit"})) { + return ($unit_name, $factor); + } + + return AM->get_base_unit($units, $unit->{"base_unit"}, $factor * $unit->{"factor"}); +} + +sub retrieve_units { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form, $type, $prefix) = @_; + + my $dbh = $form->dbconnect($myconfig); + + my $query = "SELECT *, base_unit AS original_base_unit FROM units"; + my @values; + if ($type) { + $query .= " WHERE (type = ?)"; + @values = ($type); + } + + my $sth = $dbh->prepare($query); + $sth->execute(@values) || $form->dberror($query . " (" . join(", ", @values) . ")"); + + my $units = {}; + while (my $ref = $sth->fetchrow_hashref()) { + $units->{$ref->{"name"}} = $ref; + } + $sth->finish(); + + foreach my $unit (keys(%{$units})) { + ($units->{$unit}->{"${prefix}base_unit"}, $units->{$unit}->{"${prefix}factor"}) = AM->get_base_unit($units, $unit); + } + + $dbh->disconnect(); + + $main::lxdebug->leave_sub(); + + return $units; +} + +sub units_in_use { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form, $units) = @_; + + my $dbh = $form->dbconnect($myconfig); + + foreach my $unit (values(%{$units})) { + my $base_unit = $unit->{"original_base_unit"}; + while ($base_unit) { + $units->{$base_unit}->{"DEPENDING_UNITS"} = [] unless ($units->{$base_unit}->{"DEPENDING_UNITS"}); + push(@{$units->{$base_unit}->{"DEPENDING_UNITS"}}, $unit->{"name"}); + $base_unit = $units->{$base_unit}->{"original_base_unit"}; + } + } + + foreach my $unit (values(%{$units})) { + $unit->{"in_use"} = 0; + map({ $_ = $dbh->quote($_); } @{$unit->{"DEPENDING_UNITS"}}); + + foreach my $table (qw(parts invoice orderitems)) { + my $query = "SELECT COUNT(*) FROM $table WHERE unit "; + + if (0 == scalar(@{$unit->{"DEPENDING_UNITS"}})) { + $query .= "= " . $dbh->quote($unit->{"name"}); + } else { + $query .= "IN (" . $dbh->quote($unit->{"name"}) . "," . join(",", @{$unit->{"DEPENDING_UNITS"}}) . ")"; + } + + my ($count) = $dbh->selectrow_array($query); + $form->dberror($query) if ($dbh->err); + + if ($count) { + $unit->{"in_use"} = 1; + last; + } + } + } + + $dbh->disconnect(); + + $main::lxdebug->leave_sub(); +} + +sub unit_select_data { + $main::lxdebug->enter_sub(); + + my ($self, $units, $selected, $empty_entry) = @_; + + my $select = []; + + if ($empty_entry) { + push(@{$select}, { "name" => "", "base_unit" => "", "factor" => "", "selected" => "" }); + } + + foreach my $unit (sort({ lc($a) cmp lc($b) } keys(%{$units}))) { + push(@{$select}, { "name" => $unit, + "base_unit" => $units->{$unit}->{"base_unit"}, + "factor" => $units->{$unit}->{"factor"}, + "selected" => ($unit eq $selected) ? "selected" : "" }); + } + + $main::lxdebug->leave_sub(); + + return $select; +} + +sub unit_select_html { + $main::lxdebug->enter_sub(); + + my ($self, $units, $name, $selected, $convertible_into) = @_; + + my $select = ""; + + $main::lxdebug->leave_sub(); + + return $select; +} + +sub add_unit { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form, $name, $base_unit, $factor, $type) = @_; + + my $dbh = $form->dbconnect($myconfig); + + my $query = "INSERT INTO units (name, base_unit, factor, type) VALUES (?, ?, ?, ?)"; + $dbh->do($query, undef, $name, $base_unit, $factor, $type) || $form->dberror($query . " ($name, $base_unit, $factor, $type)"); + $dbh->disconnect(); + + $main::lxdebug->leave_sub(); +} + +sub save_units { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form, $type, $units, $delete_units) = @_; + + my $dbh = $form->dbconnect_noauto($myconfig); + + my ($base_unit, $unit, $sth, $query); + if ($delete_units && (0 != scalar(@{$delete_units}))) { + $query = "DELETE FROM units WHERE name = ?"; + $sth = $dbh->prepare($query); + map({ $sth->execute($_) || $form->dberror($query . " ($_)"); } @{$delete_units}); + $sth->finish(); + } + + $query = "UPDATE units SET name = ?, base_unit = ?, factor = ? WHERE name = ?"; + $sth = $dbh->prepare($query); + + foreach $unit (values(%{$units})) { + $unit->{"depth"} = 0; + my $base_unit = $unit; + while ($base_unit->{"base_unit"}) { + $unit->{"depth"}++; + $base_unit = $units->{$base_unit->{"base_unit"}}; + } + } + + foreach $unit (sort({ $a->{"depth"} <=> $b->{"depth"} } values(%{$units}))) { + next if ($unit->{"unchanged_unit"}); + + my @values = ($unit->{"name"}, $unit->{"base_unit"}, $unit->{"factor"}, $unit->{"old_name"}); + $sth->execute(@values) || $form->dberror($query . " (" . join(", ", @values) . ")"); + } + + $sth->finish(); + $dbh->commit(); + $dbh->disconnect(); + + $main::lxdebug->leave_sub(); +} + +1;