projekt kivitendo

my ($self, $str, $beenthere) = @_;

# for Apache 2 we escape strings twice

#if (($ENV{SERVER_SOFTWARE} =~ /Apache\/2/) && !$beenthere) {

# $str = $self->escape($str, 1);

#}

for (@_) {

print qq|<input type=hidden name="$_" value="|

. $self->quote($self->{$_})

. qq|">\n|;

}

delete $self->{header};

print qq|<input type=hidden name="$_" value="|

. $self->quote($self->{$_})

. qq|">\n|;

align : "| . (shift) . qq|",

$template = XMLTemplate->new($self->{"IN"}, $self, $myconfig, $userspath);

$self->error("Outputformat not defined: $self->{'format'}");

my ($self, $dbh, $table, $field, $where, $value) = @_;

my $sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

$dbh->do($query) || $self->dberror($query);

WHERE e.curr = '$curr'

AND e.transdate = '$transdate'

FOR UPDATE|;

my $sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

WHERE curr = '$curr'

AND transdate = '$transdate'|;

VALUES ('$curr', $buy, $sell, '$transdate')|;

$dbh->do($query) || $self->dberror($query);

WHERE e.curr = '$curr'

AND e.transdate = '$transdate'|;

my $sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

my ($exchangerate) = $sth->fetchrow_array;

$sth->finish;

qq|SELECT p.terms_netto, p.terms_skonto, p.percent_skonto, | .

qq|p.description_long | .

qq|SELECT date '$transdate' + $self->{terms_netto} AS netto_date, | .

qq|date '$transdate' + $self->{terms_skonto} AS skonto_date | .

qq|FROM payment_terms LIMIT 1|;

selectrow_query($self, $dbh, $query);

qq|SELECT t.description_long, | .

qq|l.output_numberformat, l.output_dateformat, l.output_longdates | .

sub check_exchangerate {

$main::lxdebug->enter_sub();

my ($self, $myconfig, $currency, $transdate, $fld) = @_;

unless ($transdate) {

$main::lxdebug->leave_sub();

return "";

}

my $dbh = $self->dbconnect($myconfig);

my $query = qq|SELECT e.$fld FROM exchangerate e

WHERE e.curr = '$currency'

AND e.transdate = '$transdate'|;

my $sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

my ($exchangerate) = $sth->fetchrow_array;

$sth->finish;

$dbh->disconnect;

$main::lxdebug->leave_sub();

return $exchangerate;

}

my $query = qq|SELECT l.template_code FROM language l

WHERE l.id = $self->{language_id}|;

my $sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

($template_code) = $sth->fetchrow_array;

$sth->finish;

my $query = qq|SELECT p.template_code,p.printer_command FROM printers p

WHERE p.id = $self->{printer_id}|;

my $sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

($template_code, $self->{printer_command}) = $sth->fetchrow_array;

$sth->finish;

my $query = qq|SELECT s.* FROM shipto s

WHERE s.shipto_id = $self->{shipto_id}|;

my $sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

$ref = $sth->fetchrow_hashref(NAME_lc);

map { $self->{$_} = $ref->{$_} } keys %$ref;

$sth->finish;

##LINET

foreach my $item (

qw(name department_1 department_2 street zipcode city country contact phone fax email)

) {

$self->{"shipto$item"} =~ s/\'/\'\'/g;

my $query = qq| UPDATE shipto set

shiptoname = '$self->{shiptoname}',

shiptodepartment_1 = '$self->{shiptodepartment_1}',

shiptodepartment_2 = '$self->{shiptodepartment_2}',

shiptostreet = '$self->{shiptostreet}',

shiptozipcode = '$self->{shiptozipcode}',

shiptocity = '$self->{shiptocity}',

shiptocountry = '$self->{shiptocountry}',

shiptocontact = '$self->{shiptocontact}',

shiptophone = '$self->{shiptophone}',

shiptofax = '$self->{shiptofax}',

shiptoemail = '$self->{shiptoemail}'

WHERE shipto_id = $self->{shipto_id}|;

$dbh->do($query) || $self->dberror($query);

WHERE shiptoname = '$self->{shiptoname}' AND

shiptodepartment_1 = '$self->{shiptodepartment_1}' AND

shiptodepartment_2 = '$self->{shiptodepartment_2}' AND

shiptostreet = '$self->{shiptostreet}' AND

shiptozipcode = '$self->{shiptozipcode}' AND

shiptocity = '$self->{shiptocity}' AND

shiptocountry = '$self->{shiptocountry}' AND

shiptocontact = '$self->{shiptocontact}' AND

shiptophone = '$self->{shiptophone}' AND

shiptofax = '$self->{shiptofax}' AND

shiptoemail = '$self->{shiptoemail}'

| ;

my $sth = $dbh->prepare($query);

$sth->execute() || $self->dberror($query);

my $insert_check = $sth->fetch();

$sth->finish();

qq|INSERT INTO shipto (trans_id, shiptoname, shiptodepartment_1,

shiptodepartment_2, shiptostreet,

shiptozipcode, shiptocity, shiptocountry, shiptocontact,

shiptophone, shiptofax, shiptoemail, module) VALUES ($id,

'$self->{shiptoname}', '$self->{shiptodepartment_1}', '$self->{shiptodepartment_2}', '$self->{shiptostreet}',

'$self->{shiptozipcode}', '$self->{shiptocity}',

'$self->{shiptocountry}', '$self->{shiptocontact}',

'$self->{shiptophone}', '$self->{shiptofax}',

'$self->{shiptoemail}', '$module')|;

$dbh->do($query) || $self->dberror($query);

##/LINET

my $query = qq|SELECT e.id, e.name FROM employee e

WHERE e.login = '$self->{login}'|;

my $sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

($self->{employee_id}, $self->{employee}) = $sth->fetchrow_array;

$sth->finish;

my $query = qq|SELECT current_date+terms_netto FROM payment_terms

WHERE id = '$self->{payment_id}'|;

my $sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

($self->{duedate}) = $sth->fetchrow_array;

$sth->finish;

# get contacts for id, if no contact return {"","","","",""}

$self->{$key} = [];

qq|SELECT c.cp_id, c.cp_cv_id, c.cp_name, c.cp_givenname, c.cp_abteilung | .

qq|FROM contacts c | .

qq|ORDER BY lower(c.cp_name)|;

my $sth = $dbh->prepare($query);

$sth->execute($id) || $self->dberror($query . " ($id)");

my $i = 0;

while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {

push @{ $self->{$key} }, $ref;

$i++;

}

if ($i == 0) {

push @{ $self->{$key} }, { { "", "", "", "", "", "" } };

}

$sth->finish;

my $sth = $dbh->prepare($query);

$sth->execute(@values) ||

$self->dberror($query . " (" . join(", ", @values) . ")");

$self->{$key} = [];

while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {

push(@{ $self->{$key} }, $ref);

}

$sth->finish;

$self->{$key} = [];

qq|SELECT s.shipto_id,s.shiptoname,s.shiptodepartment_1 | .

qq|FROM shipto s | .

qq|WHERE s.trans_id = ?|;

my $sth = $dbh->prepare($query);

$sth->execute($vc_id) || $self->dberror($query . " ($vc_id)");

while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

push(@{ $self->{$key} }, $ref);

}

$sth->finish;

$self->{$key} = [];

my $sth = $dbh->prepare($query);

$sth->execute() || $self->dberror($query);

while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

push(@{ $self->{$key} }, $ref);

}

$sth->finish;

$self->{$key} = [];

my $sth = $dbh->prepare($query);

$sth->execute() || $self->dberror($query);

while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

push(@{ $self->{$key} }, $ref);

}

$sth->finish;

$self->{$key} = [];

my $sth = $dbh->prepare($query);

$sth->execute() || $self->dberror($query);

while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

push(@{ $self->{$key} }, $ref);

}

$sth->finish;

my $name = $self->like(lc $self->{$table});

my $customernumber = $self->like(lc $self->{customernumber});

if ($self->{customernumber} ne "") {

$query = qq~SELECT c.id, c.name,

c.street || ' ' || c.zipcode || ' ' || c.city || ' ' || c.country AS address

FROM $table c

WHERE (lower(c.customernumber) LIKE '$customernumber') AND (not c.obsolete)

ORDER BY c.name~;

} else {

$query = qq~SELECT c.id, c.name,

c.street || ' ' || c.zipcode || ' ' || c.city || ' ' || c.country AS address

FROM $table c

WHERE (lower(c.name) LIKE '$name') AND (not c.obsolete)

ORDER BY c.name~;

}

if ($self->{openinvoices}) {

$query = qq~SELECT DISTINCT c.id, c.name,

c.street || ' ' || c.zipcode || ' ' || c.city || ' ' || c.country AS address

FROM $self->{arap} a

JOIN $table c ON (a.${table}_id = c.id)

WHERE NOT a.amount = a.paid

AND lower(c.name) LIKE '$name'

ORDER BY c.name~;

}

my $sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

my $i = 0;

while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

push(@{ $self->{name_list} }, $ref);

$i++;

$sth->finish;

$dbh->disconnect;

return $i;

my $sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

my ($count) = $sth->fetchrow_array;

$sth->finish;

FROM $table WHERE not obsolete

ORDER BY name|;

$sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

push @{ $self->{"all_$table"} }, $ref;

}

$sth->finish;

FROM employee e

WHERE e.sales = '1'

AND NOT e.id = $self->{employee_id}|;

$sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

push @{ $self->{all_employees} }, $ref;

}

$sth->finish;

push @{ $self->{all_employees} },

{ id => $self->{employee_id},

name => $self->{employee} };

$query = qq|SELECT d.id, d.description

FROM department d

WHERE d.role = 'P'

ORDER BY 2|;

$query = qq|SELECT d.id, d.description

FROM department d

ORDER BY 2|;

$sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

push @{ $self->{all_departments} }, $ref;

}

$sth->finish;

ORDER BY 1|;

$sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

push @{ $self->{languages} }, $ref;

}

$sth->finish;

ORDER BY 1|;

$sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

push @{ $self->{printers} }, $ref;

}

$sth->finish;

$sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

push @{ $self->{payment_terms} }, $ref;

}

$sth->finish;

undef $self->{languages};

undef $self->{payment_terms};

undef $self->{printers};

my $ref;

FROM language

ORDER BY 1|;

my $sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

push @{ $self->{languages} }, $ref;

}

$sth->finish;

ORDER BY 1|;

$sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

push @{ $self->{printers} }, $ref;

}

$sth->finish;

$sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

push @{ $self->{payment_terms} }, $ref;

}

$sth->finish;

$sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

$self->{BUCHUNGSGRUPPEN} = [];

while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {

push @{ $self->{BUCHUNGSGRUPPEN} }, $ref;

}

$sth->finish;

my $dbh = $self->dbconnect($myconfig);

my $where = "1 = 1";

if (defined $table) {

if ($table eq 'customer') {

$where = " d.role = 'P'";

}

my $query = qq|SELECT d.id, d.description

FROM department d

WHERE $where

ORDER BY 2|;

my $sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {

push @{ $self->{all_departments} }, $ref;

}

$sth->finish;

my ($query, $sth);

$transdate = qq|'$self->{transdate}'|;

WHERE c.link LIKE '%$module%' AND c.id=tk.chart_id AND tk.id =

(SELECT id FROM taxkeys where taxkeys.chart_id = c.id AND startdate <= $transdate ORDER BY startdate desc LIMIT 1)

do_statement($form, $sth, $query);

while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {

$query = qq| SELECT * FROM tax t|;

my $arap = ($table eq 'customer') ? 'ar' : 'ap';

$query = qq|SELECT a.cp_id, a.invnumber, a.transdate,

a.${table}_id, a.datepaid, a.duedate, a.ordnumber,

a.taxincluded, a.curr AS currency, a.notes, a.intnotes,

c.name AS $table, a.department_id, d.description AS department,

a.amount AS oldinvtotal, a.paid AS oldtotalpaid,

a.employee_id, e.name AS employee, a.gldate, a.type

FROM $arap a

JOIN $table c ON (a.${table}_id = c.id)

LEFT JOIN employee e ON (e.id = a.employee_id)

LEFT JOIN department d ON (d.id = a.department_id)

WHERE a.id = $self->{id}|;

$sth = $dbh->prepare($query);

do_statement($form, $sth, $query);

$ref = $sth->fetchrow_hashref(NAME_lc);

$sth->finish;

$transdate = qq|'$self->{transdate}'|;

SELECT

c.accno,

c.description,

c.link,

c.taxkey_id,

tk.tax_id

FROM chart c

LEFT JOIN taxkeys tk ON (tk.chart_id = c.id)

WHERE

c.link LIKE ?

AND

(tk.chart_id = c.id AND NOT c.link like '%_tax%')

OR (NOT tk.chart_id = c.id AND c.link like '%_tax%')

AND

tk.id = ( SELECT id from taxkeys

WHERE taxkeys.chart_id = c.id

AND startdate <= ?

ORDER BY startdate desc LIMIT 1

)

ORDER BY c.accno|;

do_statement($form, $sth, $query, "%$module%", $transdate);

while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {

$query = qq|SELECT c.accno, c.description, a.source, a.amount, a.memo,

a.transdate, a.cleared, a.project_id, p.projectnumber, a.taxkey, t.rate, t.id

FROM acc_trans a

JOIN chart c ON (c.id = a.chart_id)

LEFT JOIN project p ON (p.id = a.project_id)

LEFT JOIN tax t ON (t.id=(SELECT tk.tax_id from taxkeys tk WHERE (tk.taxkey_id=a.taxkey) AND ((CASE WHEN a.chart_id IN (SELECT chart_id FROM taxkeys WHERE taxkey_id=a.taxkey) THEN tk.chart_id=a.chart_id ELSE 1=1 END) OR (c.link='%tax%')) AND startdate <=a.transdate ORDER BY startdate DESC LIMIT 1))

WHERE a.trans_id = $self->{id}

AND a.fx_transaction = '0'

ORDER BY a.oid,a.transdate|;

do_statement($form, $sth, $query);

my $fld = ($table eq 'customer') ? 'buy' : 'sell';

$self->get_exchangerate($dbh, $self->{currency}, $self->{transdate},

$fld);

$self->get_exchangerate($dbh, $self->{currency}, $ref->{transdate},

$fld);

$query = qq|SELECT d.curr AS currencies, d.closedto, d.revtrans,

(SELECT c.accno FROM chart c

WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,

(SELECT c.accno FROM chart c

WHERE d.fxloss_accno_id = c.id) AS fxloss_accno

FROM defaults d|;

$sth = $dbh->prepare($query);

do_statement($form, $sth, $query);

$ref = $sth->fetchrow_hashref(NAME_lc);

$sth->finish;

$query = qq|SELECT current_date AS transdate,

d.curr AS currencies, d.closedto, d.revtrans,

(SELECT c.accno FROM chart c

WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,

(SELECT c.accno FROM chart c

WHERE d.fxloss_accno_id = c.id) AS fxloss_accno

FROM defaults d|;

$sth = $dbh->prepare($query);

do_statement($form, $sth, $query);

$ref = $sth->fetchrow_hashref(NAME_lc);

$sth->finish;

my $fld = ($table eq 'customer') ? 'buy' : 'sell';

$self->get_exchangerate($dbh, $self->{currency}, $self->{transdate},

$fld);

$sth->finish;

WHERE $where

AND ${table}_id > 0|;

my $sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

my ($trans_id) = $sth->fetchrow_array;

$sth->finish;

$query = qq|SELECT ct.name, a.curr, a.${table}_id,

current_date + ct.terms AS duedate, a.department_id,

d.description AS department

FROM $arap a

JOIN $table ct ON (a.${table}_id = ct.id)

LEFT JOIN department d ON (a.department_id = d.id)

WHERE a.id = $trans_id|;

$sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

($self->{$table}, $self->{currency}, $self->{"${table}_id"},

$self->{duedate}, $self->{department_id}, $self->{department})

= $sth->fetchrow_array;

$sth->finish;

my ($sth, $query);

$query = qq|SELECT to_date('$thisdate', '$dateformat') + $days AS thisdate

FROM defaults|;

$sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

$query = qq|SELECT current_date AS thisdate

FROM defaults|;

$sth = $dbh->prepare($query);

$sth->execute || $self->dberror($query);

($thisdate) = $sth->fetchrow_array;

$sth->finish;

WHERE formname = '$self->{formname}'

AND trans_id = ?|;

my $sth = $dbh->prepare($query) || $self->dberror($query);

$sth->execute($self->{"id_$i"} * 1) || $self->dberror($query);

$sth->finish;

$sth->execute($self->{id}) || $self->dberror($query);

$sth->finish;

$query = qq|INSERT INTO status (trans_id, printed, spoolfile, formname,

chart_id) VALUES (?, '$printed',

'$queued{$self->{formname}}', '$self->{prinform}',

(SELECT c.id FROM chart c WHERE c.accno = '$accno'))|;

$sth = $dbh->prepare($query) || $self->dberror($query);

$sth->execute($self->{"id_$i"}) || $self->dberror($query);

$sth->finish;

$query = qq|INSERT INTO status (trans_id, printed, emailed,

spoolfile, formname)

VALUES ($self->{id}, '$printed', '$emailed',

'$queued{$self->{formname}}', '$self->{formname}')|;

$dbh->do($query) || $self->dberror($query);

#--- 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('invoice')

# $main::locale->text('proforma')

# $main::locale->text('sales_order')

# $main::locale->text('packing_list')

# $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();

if(!exists $self->{employee_id}) {

&get_employee($self, $dbh);

}

my $query =

qq|INSERT INTO history_erp (trans_id, employee_id, addition, what_done) | .

qq|VALUES (?, ?, ?, ?)|;

my @values = (conv_i($self->{id}), conv_i($self->{employee_id}),

$self->{addition}, $self->{what_done});

do_query($self, $dbh, $query, @values);

$main::lxdebug->leave_sub();

}

sub get_history {

$main::lxdebug->enter_sub();

my $self = shift();

my $dbh = shift();

my $trans_id = shift();

my $restriction = shift();

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 | .

qq|FROM history_erp h | .

qq|LEFT JOIN employee emp | .

qq|ON emp.id = h.employee_id | .

qq|WHERE trans_id = ? |

. $restriction;

my $sth = $dbh->prepare($query) || $self->dberror($query);

$sth->execute($trans_id) || $self->dberror("$query ($trans_id)");

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});

$tempArray[$i++] = $hash_ref;

}

$main::lxdebug->leave_sub() and return \@tempArray

if ($i > 0 && $tempArray[0] ne "");

}

$main::lxdebug->leave_sub();

return 0;

}

$query = qq|DELETE FROM status

WHERE formname = '$self->{formname}'

AND trans_id = $self->{id}|;

$dbh->do($query) || $self->dberror($query);