-sub get_account {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
- my $query =
- qq!SELECT c.accno, c.description, c.charttype, c.category,! .
- qq! c.link, c.pos_bilanz, c.pos_eur, c.new_chart_id, c.valid_from, ! .
- qq! c.pos_bwa, ! .
- qq! tk.taxkey_id, tk.pos_ustva, tk.tax_id, ! .
- qq! tk.tax_id || '--' || tk.taxkey_id AS tax, tk.startdate ! .
- qq!FROM chart c ! .
- qq!LEFT JOIN taxkeys tk ! .
- qq!ON (c.id=tk.chart_id AND tk.id = ! .
- qq! (SELECT id FROM taxkeys ! .
- qq! WHERE taxkeys.chart_id = c.id AND startdate <= current_date ! .
- qq! ORDER BY startdate DESC LIMIT 1)) ! .
- qq!WHERE c.id = ?!;
-
- my $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
-
- my $ref = $sth->fetchrow_hashref(NAME_lc);
-
- foreach my $key (keys %$ref) {
- $form->{"$key"} = $ref->{"$key"};
- }
-
- $sth->finish;
-
- # get default accounts
- $query = qq|SELECT inventory_accno_id, income_accno_id, expense_accno_id
- FROM defaults|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
-
- map { $form->{$_} = $ref->{$_} } keys %ref;
-
- $sth->finish;
-
- # get taxkeys and description
- $query = qq§SELECT id, taxkey,id||'--'||taxkey AS tax, taxdescription
- FROM tax ORDER BY taxkey§;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $form->{TAXKEY} = [];
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{TAXKEY} }, $ref;
- }
-
- $sth->finish;
- if ($form->{id}) {
- # get new accounts
- $query = qq|SELECT id, accno,description
- FROM chart WHERE link = ?|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{link}) || $form->dberror($query . " ($form->{link})");
-
- $form->{NEWACCOUNT} = [];
- 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 = ?|;
- $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
-
- ($form->{orphaned}) = $sth->fetchrow_array;
- $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 = ?|;
- my ($count) = selectrow_query($form, $dbh, $query, $form->{id});
- if ($count >=0) {
- $form->{new_chart_valid} = 1;
- }
- $sth->finish;
- }
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub save_account {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database, turn off AutoCommit
- my $dbh = $form->dbconnect_noauto($myconfig);
-
- # sanity check, can't have AR with AR_...
- if ($form->{AR} || $form->{AP} || $form->{IC}) {
- map { delete $form->{$_} }
- qw(AR_amount AR_tax AR_paid AP_amount AP_tax AP_paid IC_sale IC_cogs IC_taxpart IC_income IC_expense IC_taxservice CT_tax);
- }
-
- $form->{link} = "";
- foreach my $item ($form->{AR}, $form->{AR_amount},
- $form->{AR_tax}, $form->{AR_paid},
- $form->{AP}, $form->{AP_amount},
- $form->{AP_tax}, $form->{AP_paid},
- $form->{IC}, $form->{IC_sale},
- $form->{IC_cogs}, $form->{IC_taxpart},
- $form->{IC_income}, $form->{IC_expense},
- $form->{IC_taxservice}, $form->{CT_tax}
- ) {
- $form->{link} .= "${item}:" if ($item);
- }
- chop $form->{link};
-
- # strip blanks from accno
- map { $form->{$_} =~ s/ //g; } qw(accno);
-
- my ($query, $sth);
-
- if ($form->{id} eq "NULL") {
- $form->{id} = "";
- }
-
- my @values;
-
- my ($tax_id, $taxkey) = split(/--/, $form->{tax});
- my $startdate = $form->{startdate} ? $form->{startdate} : "1970-01-01";
-
- if ($form->{id}) {
- $query = qq|UPDATE chart SET
- accno = ?, description = ?, charttype = ?,
- category = ?, link = ?,
- taxkey_id = ?,
- pos_ustva = ?, pos_bwa = ?, pos_bilanz = ?,
- pos_eur = ?, new_chart_id = ?, valid_from = ?
- WHERE id = ?|;
- @values = ($form->{accno}, $form->{description}, $form->{charttype},
- $form->{category}, $form->{link},
- conv_i($taxkey),
- conv_i($form->{pos_ustva}), conv_i($form->{pos_bwa}),
- conv_i($form->{pos_bilanz}), conv_i($form->{pos_eur}),
- conv_i($form->{new_chart_id}),
- conv_date($form->{valid_from}),
- $form->{id});
-
- } elsif ($form->{id} && !$form->{new_chart_valid}) {
- $query = qq|UPDATE chart SET new_chart_id = ?, valid_from = ?
- WHERE id = ?|;
- @values = (conv_i($form->{new_chart_id}), conv_date($form->{valid_from}),
- $form->{id});
- } else {
- $query = qq|INSERT INTO chart
- (accno, description, charttype,
- category, link, taxkey_id,
- pos_ustva, pos_bwa, pos_bilanz, pos_eur,
- new_chart_id, valid_from)
- VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
- @values = ($form->{accno}, $form->{description}, $form->{charttype},
- $form->{category}, $form->{link}, conv_i($taxkey),
- conv_i($form->{pos_ustva}), conv_i($form->{pos_bwa}),
- conv_i($form->{pos_bilanz}), conv_i($form->{pos_eur}),
- conv_i($form->{new_chart_id}),
- conv_date($form->{valid_from}));
-
- }
- do_query($form, $dbh, $query, @values);
-
- #Save Taxes
- if (!$form->{id}) {
- $query =
- qq|INSERT INTO taxkeys | .
- qq|(chart_id, tax_id, taxkey_id, pos_ustva, startdate) | .
- qq|VALUES ((SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?)|;
- do_query($form, $dbh, $query,
- $form->{accno}, conv_i($tax_id), conv_i($taxkey),
- conv_i($form->{pos_ustva}), conv_date($startdate));
-
- } else {
- $query = qq|DELETE FROM taxkeys WHERE chart_id = ? AND tax_id = ?|;
- do_query($form, $dbh, $query, $form->{id}, conv_i($tax_id));
-
- $query =
- qq|INSERT INTO taxkeys | .
- qq|(chart_id, tax_id, taxkey_id, pos_ustva, startdate) | .
- qq|VALUES (?, ?, ?, ?, ?)|;
- do_query($form, $dbh, $query,
- $form->{id}, conv_i($tax_id), conv_i($taxkey),
- conv_i($form->{pos_ustva}), conv_date($startdate));
- }
-
- # commit
- my $rc = $dbh->commit;
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-
- return $rc;
-}
-
-sub delete_account {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database, turn off AutoCommit
- my $dbh = $form->dbconnect_noauto($myconfig);
-
- my $query = qq|SELECT count(*) FROM acc_trans a
- WHERE a.chart_id = ?|;
- my ($count) = selectrow_query($form, $dbh, $query, $form->{id});
-
- if ($count) {
- $dbh->disconnect;
- $main::lxdebug->leave_sub();
- return;
- }
-
- # set inventory_accno_id, income_accno_id, expense_accno_id to defaults
- foreach my $type (qw(inventory income expense)) {
- $query =
- qq|UPDATE parts | .
- qq|SET ${type}_accno_id = (SELECT ${type}_accno_id FROM defaults) | .
- qq|WHERE ${type}_accno_id = ?|;
- do_query($form, $dbh, $query, $form->{id});
- }
-
- foreach my $table (qw(partstax customertax vendortax tax)) {
- $query = qq|DELETE FROM $table
- WHERE chart_id = ?|;
- do_query($form, $dbh, $query, $form->{id});
- }
-
- # delete chart of account record
- $query = qq|DELETE FROM chart
- WHERE id = ?|;
- do_query($form, $dbh, $query, $form->{id});
-
- # commit and redirect
- my $rc = $dbh->commit;
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-
- return $rc;
-}
-
-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);
-
- $form->{ALL} = [];
- 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 = ?|;
- my $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
-
- 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->{orphaned}) = selectrow_query($form, $dbh, $query, $form->{id});
-
- $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);
-
- my @values = ($form->{description}, $form->{role});
- if ($form->{id}) {
- $query = qq|UPDATE department SET
- description = ?, role = ?
- WHERE id = ?|;
- push(@values, $form->{id});
- } else {
- $query = qq|INSERT INTO department
- (description, role)
- VALUES (?, ?)|;
- }
- do_query($form, $dbh, $query, @values);
-
- $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 = ?|;
- do_query($form, $dbh, $query, $form->{id});
-
- $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);
-
- $form->{ALL};
- 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 | .
- qq|FROM leads l | .
- qq|WHERE l.id = ?|;
- my $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
-
- 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);
-
- my @values = ($form->{description});
- # id is the old record
- if ($form->{id}) {
- $query = qq|UPDATE leads SET
- lead = ?
- WHERE id = ?|;
- puhs(@values, $form->{id});
- } else {
- $query = qq|INSERT INTO leads
- (lead)
- VALUES (?)|;
- }
- do_query($form, $dbh, $query, @values);
-
- $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 = ?|;
- do_query($form, $dbh, $query, $form->{id});
-
- $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
- FROM business
- ORDER BY 2|;
-
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $form->{ALL};
- 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
- FROM business b
- WHERE b.id = ?|;
- my $sth = $dbh->prepare($query);
- $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
-
- 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);
-
- my @values = ($form->{description}, $form->{discount},
- $form->{customernumberinit});
- # id is the old record
- if ($form->{id}) {
- $query = qq|UPDATE business SET
- description = ?,
- discount = ?,
- customernumberinit = ?
- WHERE id = ?|;
- push(@values, $form->{id});
- } else {
- $query = qq|INSERT INTO business
- (description, discount, customernumberinit)
- VALUES (?, ?, ?)|;
- }
- do_query($form, $dbh, $query, @values);
-
- $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 = ?|;
- do_query($form, $dbh, $query, $form->{id});
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-
-sub language {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form, $return_list) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- my $query =
- "SELECT id, description, template_code, article_code, " .
- " output_numberformat, output_dateformat, output_longdates " .
- "FROM language ORDER BY description";
-
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- my $ary = [];
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- push(@{ $ary }, $ref);
- }
-
- $sth->finish;
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-
- if ($return_list) {
- return @{$ary};
- } else {
- $form->{ALL} = $ary;
- }
-}
-
-sub get_language {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- my $query =
- "SELECT description, template_code, article_code, " .
- " output_numberformat, output_dateformat, output_longdates " .
- "FROM language WHERE id = ?";
- my $sth = $dbh->prepare($query);
- $sth->execute($form->{"id"}) || $form->dberror($query . " ($form->{id})");
-
- my $ref = $sth->fetchrow_hashref(NAME_lc);
-
- map { $form->{$_} = $ref->{$_} } keys %$ref;
-
- $sth->finish;
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub get_language_details {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form, $id) = @_;
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);