+ my $column_spec = join(', ', map { "${_} AS $column_map{$_}" } keys %column_map);
+ $query = qq|SELECT $column_spec
+ FROM $arap a
+ LEFT JOIN $table ct ON (a.${table}_id = ct.id)
+ LEFT JOIN department d ON (a.department_id = d.id)
+ LEFT JOIN currencies cu ON (cu.id=ct.currency_id)
+ WHERE a.id = ?|;
+ my $ref = selectfirst_hashref_query($self, $dbh, $query, $trans_id);
+
+ map { $self->{$_} = $ref->{$_} } values %column_map;
+
+ $main::lxdebug->leave_sub();
+}
+
+sub current_date {
+ $main::lxdebug->enter_sub();
+
+ my $self = shift;
+ my $myconfig = shift || \%::myconfig;
+ my ($thisdate, $days) = @_;
+
+ my $dbh = $self->get_standard_dbh($myconfig);
+ my $query;
+
+ $days *= 1;
+ if ($thisdate) {
+ my $dateformat = $myconfig->{dateformat};
+ $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
+ $thisdate = $dbh->quote($thisdate);
+ $query = qq|SELECT to_date($thisdate, '$dateformat') + $days AS thisdate|;
+ } else {
+ $query = qq|SELECT current_date AS thisdate|;
+ }
+
+ ($thisdate) = selectrow_query($self, $dbh, $query);
+
+ $main::lxdebug->leave_sub();
+
+ return $thisdate;
+}
+
+sub like {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $string) = @_;
+
+ if ($string !~ /%/) {
+ $string = "%$string%";
+ }
+
+ $string =~ s/\'/\'\'/g;
+
+ $main::lxdebug->leave_sub();
+
+ return $string;
+}
+
+sub redo_rows {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $flds, $new, $count, $numrows) = @_;
+
+ my @ndx = ();
+
+ map { push @ndx, { num => $new->[$_ - 1]->{runningnumber}, ndx => $_ } } 1 .. $count;
+
+ my $i = 0;
+
+ # fill rows
+ foreach my $item (sort { $a->{num} <=> $b->{num} } @ndx) {
+ $i++;
+ my $j = $item->{ndx} - 1;
+ map { $self->{"${_}_$i"} = $new->[$j]->{$_} } @{$flds};
+ }
+
+ # delete empty rows
+ for $i ($count + 1 .. $numrows) {
+ map { delete $self->{"${_}_$i"} } @{$flds};
+ }
+
+ $main::lxdebug->leave_sub();
+}
+
+sub update_status {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig) = @_;
+
+ my ($i, $id);
+
+ my $dbh = $self->dbconnect_noauto($myconfig);
+
+ my $query = qq|DELETE FROM status
+ WHERE (formname = ?) AND (trans_id = ?)|;
+ my $sth = prepare_query($self, $dbh, $query);
+
+ if ($self->{formname} =~ /(check|receipt)/) {
+ for $i (1 .. $self->{rowcount}) {
+ do_statement($self, $sth, $query, $self->{formname}, $self->{"id_$i"} * 1);
+ }
+ } else {
+ do_statement($self, $sth, $query, $self->{formname}, $self->{id});
+ }
+ $sth->finish();
+
+ my $printed = ($self->{printed} =~ /\Q$self->{formname}\E/) ? "1" : "0";
+ my $emailed = ($self->{emailed} =~ /\Q$self->{formname}\E/) ? "1" : "0";
+
+ my %queued = split / /, $self->{queued};
+ my @values;
+
+ if ($self->{formname} =~ /(check|receipt)/) {
+
+ # this is a check or receipt, add one entry for each lineitem
+ my ($accno) = split /--/, $self->{account};
+ $query = qq|INSERT INTO status (trans_id, printed, spoolfile, formname, chart_id)
+ VALUES (?, ?, ?, ?, (SELECT c.id FROM chart c WHERE c.accno = ?))|;
+ @values = ($printed, $queued{$self->{formname}}, $self->{prinform}, $accno);
+ $sth = prepare_query($self, $dbh, $query);
+
+ for $i (1 .. $self->{rowcount}) {
+ if ($self->{"checked_$i"}) {
+ do_statement($self, $sth, $query, $self->{"id_$i"}, @values);
+ }
+ }
+ $sth->finish();
+
+ } else {
+ $query = qq|INSERT INTO status (trans_id, printed, emailed, spoolfile, formname)
+ VALUES (?, ?, ?, ?, ?)|;
+ do_query($self, $dbh, $query, $self->{id}, $printed, $emailed,
+ $queued{$self->{formname}}, $self->{formname});
+ }
+
+ $dbh->commit;
+ $dbh->disconnect;
+
+ $main::lxdebug->leave_sub();
+}
+
+sub save_status {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh) = @_;
+
+ my ($query, $printed, $emailed);
+
+ my $formnames = $self->{printed};
+ my $emailforms = $self->{emailed};
+
+ $query = qq|DELETE FROM status
+ WHERE (formname = ?) AND (trans_id = ?)|;
+ do_query($self, $dbh, $query, $self->{formname}, $self->{id});
+
+ # this only applies to the forms
+ # checks and receipts are posted when printed or queued
+
+ if ($self->{queued}) {
+ my %queued = split / /, $self->{queued};
+
+ foreach my $formname (keys %queued) {
+ $printed = ($self->{printed} =~ /\Q$self->{formname}\E/) ? "1" : "0";
+ $emailed = ($self->{emailed} =~ /\Q$self->{formname}\E/) ? "1" : "0";
+
+ $query = qq|INSERT INTO status (trans_id, printed, emailed, spoolfile, formname)
+ VALUES (?, ?, ?, ?, ?)|;
+ do_query($self, $dbh, $query, $self->{id}, $printed, $emailed, $queued{$formname}, $formname);
+
+ $formnames =~ s/\Q$self->{formname}\E//;
+ $emailforms =~ s/\Q$self->{formname}\E//;
+
+ }
+ }
+
+ # save printed, emailed info
+ $formnames =~ s/^ +//g;
+ $emailforms =~ s/^ +//g;
+
+ my %status = ();
+ map { $status{$_}{printed} = 1 } split / +/, $formnames;
+ map { $status{$_}{emailed} = 1 } split / +/, $emailforms;
+
+ foreach my $formname (keys %status) {
+ $printed = ($formnames =~ /\Q$self->{formname}\E/) ? "1" : "0";
+ $emailed = ($emailforms =~ /\Q$self->{formname}\E/) ? "1" : "0";
+
+ $query = qq|INSERT INTO status (trans_id, printed, emailed, formname)
+ VALUES (?, ?, ?, ?)|;
+ do_query($self, $dbh, $query, $self->{id}, $printed, $emailed, $formname);
+ }
+
+ $main::lxdebug->leave_sub();
+}
+
+#--- 4 locale ---#
+# $main::locale->text('SAVED')
+# $main::locale->text('DELETED')
+# $main::locale->text('ADDED')
+# $main::locale->text('PAYMENT POSTED')
+# $main::locale->text('POSTED')
+# $main::locale->text('POSTED AS NEW')
+# $main::locale->text('ELSE')
+# $main::locale->text('SAVED FOR DUNNING')
+# $main::locale->text('DUNNING STARTED')
+# $main::locale->text('PRINTED')
+# $main::locale->text('MAILED')
+# $main::locale->text('SCREENED')
+# $main::locale->text('CANCELED')
+# $main::locale->text('invoice')
+# $main::locale->text('proforma')
+# $main::locale->text('sales_order')
+# $main::locale->text('pick_list')
+# $main::locale->text('purchase_order')
+# $main::locale->text('bin_list')
+# $main::locale->text('sales_quotation')
+# $main::locale->text('request_quotation')
+
+sub save_history {
+ $main::lxdebug->enter_sub();
+
+ my $self = shift;
+ my $dbh = shift || $self->get_standard_dbh;
+
+ if(!exists $self->{employee_id}) {
+ &get_employee($self, $dbh);
+ }
+
+ my $query =
+ qq|INSERT INTO history_erp (trans_id, employee_id, addition, what_done, snumbers) | .
+ qq|VALUES (?, (SELECT id FROM employee WHERE login = ?), ?, ?, ?)|;
+ my @values = (conv_i($self->{id}), $self->{login},
+ $self->{addition}, $self->{what_done}, "$self->{snumbers}");
+ do_query($self, $dbh, $query, @values);
+
+ $dbh->commit;
+
+ $main::lxdebug->leave_sub();
+}
+
+sub get_history {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $dbh, $trans_id, $restriction, $order) = @_;
+ my ($orderBy, $desc) = split(/\-\-/, $order);
+ $order = " ORDER BY " . ($order eq "" ? " h.itime " : ($desc == 1 ? $orderBy . " DESC " : $orderBy . " "));
+ my @tempArray;
+ my $i = 0;
+ if ($trans_id ne "") {
+ my $query =
+ qq|SELECT h.employee_id, h.itime::timestamp(0) AS itime, h.addition, h.what_done, emp.name, h.snumbers, h.trans_id AS id | .
+ qq|FROM history_erp h | .
+ qq|LEFT JOIN employee emp ON (emp.id = h.employee_id) | .
+ qq|WHERE (trans_id = | . $trans_id . qq|) $restriction | .
+ $order;
+
+ my $sth = $dbh->prepare($query) || $self->dberror($query);
+
+ $sth->execute() || $self->dberror("$query");
+
+ while(my $hash_ref = $sth->fetchrow_hashref()) {
+ $hash_ref->{addition} = $main::locale->text($hash_ref->{addition});
+ $hash_ref->{what_done} = $main::locale->text($hash_ref->{what_done});
+ $hash_ref->{snumbers} =~ s/^.+_(.*)$/$1/g;
+ $tempArray[$i++] = $hash_ref;
+ }
+ $main::lxdebug->leave_sub() and return \@tempArray
+ if ($i > 0 && $tempArray[0] ne "");
+ }
+ $main::lxdebug->leave_sub();
+ return 0;
+}
+
+sub get_partsgroup {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $p) = @_;
+ my $target = $p->{target} || 'all_partsgroup';
+
+ my $dbh = $self->get_standard_dbh($myconfig);
+
+ my $query = qq|SELECT DISTINCT pg.id, pg.partsgroup
+ FROM partsgroup pg
+ JOIN parts p ON (p.partsgroup_id = pg.id) |;
+ my @values;
+
+ if ($p->{searchitems} eq 'part') {
+ $query .= qq|WHERE p.inventory_accno_id > 0|;
+ }
+ if ($p->{searchitems} eq 'service') {
+ $query .= qq|WHERE p.inventory_accno_id IS NULL|;
+ }
+ if ($p->{searchitems} eq 'assembly') {
+ $query .= qq|WHERE p.assembly = '1'|;
+ }
+ if ($p->{searchitems} eq 'labor') {
+ $query .= qq|WHERE (p.inventory_accno_id > 0) AND (p.income_accno_id IS NULL)|;
+ }
+
+ $query .= qq|ORDER BY partsgroup|;
+
+ if ($p->{all}) {
+ $query = qq|SELECT id, partsgroup FROM partsgroup
+ ORDER BY partsgroup|;
+ }
+
+ if ($p->{language_code}) {
+ $query = qq|SELECT DISTINCT pg.id, pg.partsgroup,
+ t.description AS translation
+ FROM partsgroup pg
+ JOIN parts p ON (p.partsgroup_id = pg.id)
+ LEFT JOIN translation t ON ((t.trans_id = pg.id) AND (t.language_code = ?))
+ ORDER BY translation|;
+ @values = ($p->{language_code});
+ }
+
+ $self->{$target} = selectall_hashref_query($self, $dbh, $query, @values);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub get_pricegroup {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $p) = @_;
+
+ my $dbh = $self->get_standard_dbh($myconfig);
+
+ my $query = qq|SELECT p.id, p.pricegroup
+ FROM pricegroup p|;
+
+ $query .= qq| ORDER BY pricegroup|;
+
+ if ($p->{all}) {
+ $query = qq|SELECT id, pricegroup FROM pricegroup
+ ORDER BY pricegroup|;
+ }
+
+ $self->{all_pricegroup} = selectall_hashref_query($self, $dbh, $query);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub all_years {
+# usage $form->all_years($myconfig, [$dbh])
+# return list of all years where bookings found
+# (@all_years)
+
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $dbh) = @_;
+
+ $dbh ||= $self->get_standard_dbh($myconfig);
+
+ # get years
+ my $query = qq|SELECT (SELECT MIN(transdate) FROM acc_trans),
+ (SELECT MAX(transdate) FROM acc_trans)|;
+ my ($startdate, $enddate) = selectrow_query($self, $dbh, $query);
+
+ if ($myconfig->{dateformat} =~ /^yy/) {
+ ($startdate) = split /\W/, $startdate;
+ ($enddate) = split /\W/, $enddate;
+ } else {
+ (@_) = split /\W/, $startdate;
+ $startdate = $_[2];
+ (@_) = split /\W/, $enddate;
+ $enddate = $_[2];
+ }
+
+ my @all_years;
+ $startdate = substr($startdate,0,4);
+ $enddate = substr($enddate,0,4);
+
+ while ($enddate >= $startdate) {
+ push @all_years, $enddate--;
+ }
projects / mfinanz.git / blobdiff