projekt kivitendo

#====================================================================

# Copyright (C) 2004

# Based on SQL-Ledger Version 2.1.9

# Web http://www.lx-office.org

#

#=====================================================================

# SQL-Ledger Accounting

# Copyright (C) 1999-2003

#

# Author: Dieter Simader

# Email: dsimader@sql-ledger.org

# Web: http://www.sql-ledger.org

#

# Contributors:

#

# This program is free software; you can redistribute it and/or modify

# it under the terms of the GNU General Public License as published by

# the Free Software Foundation; either version 2 of the License, or

# (at your option) any later version.

#

# This program is distributed in the hope that it will be useful,

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License

# along with this program; if not, write to the Free Software

# MA 02110-1335, USA.

#

# Order entry module

# Quotation

#======================================================================

package OE;

use SL::DB::ProjectType;

$main::lxdebug->enter_sub();

my ($self, $myconfig, $form) = @_;

# connect to database

my $query;

my $ordnumber = 'ordnumber';

my $quotation = '0';

if ($form->{type} =~ /_quotation$/) {

$quotation = '1';

$ordnumber = 'quonumber';

} elsif ($form->{type} eq 'sales_order') {

$periodic_invoices_columns = qq| , COALESCE(pcfg.active, 'f') AS periodic_invoices |;

$periodic_invoices_joins = qq| LEFT JOIN periodic_invoices_configs pcfg ON (o.id = pcfg.oe_id) |;

my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";

my %billed_netamount;

if ($form->{l_remaining_amount} || $form->{l_remaining_netamount}) {

$query = <<'';

SELECT from_id, ar.amount, ar.netamount FROM (

SELECT from_id, to_id

FROM record_links

WHERE from_table = 'oe' AND to_table = 'ar'

UNION

SELECT rl1.from_id, rl2.to_id

FROM record_links rl1

LEFT JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)

WHERE rl1.from_table = 'oe' AND rl2.to_table = 'ar'

SELECT rl1.from_id, rl3.to_id

FROM record_links rl1

JOIN record_links rl2 ON (rl1.to_table = rl2.from_table AND rl1.to_id = rl2.from_id)

JOIN record_links rl3 ON (rl2.to_table = rl3.from_table AND rl2.to_id = rl3.from_id)

LEFT JOIN ar ON ar.id = rl.to_id

for my $ref (@{ selectall_hashref_query($form, $dbh, $query) }) {

$billed_amount{ $ref->{from_id}} += $ref->{amount};

$billed_netamount{$ref->{from_id}} += $ref->{netamount};

}

}

$form->{phone_notes} = trim($form->{phone_notes});

if ($form->{phone_notes}) {

$phone_notes_columns = qq| , phone_notes.subject AS phone_notes_subject, phone_notes.body AS phone_notes_body |;

$phone_notes_join = qq| JOIN notes phone_notes ON (o.id = phone_notes.trans_id AND phone_notes.trans_module LIKE 'oe') |;

}

qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .

qq| order_statuses.name AS order_status | .

qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .

qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .

push(@values, $quotation);

if ($form->{"project_id"}) {

qq| (SELECT * FROM orderitems oi | .

qq| WHERE oi.project_id = ? AND oi.trans_id = o.id))|;

}

if ($form->{"projectnumber"}) {

$query .= <<SQL;

LEFT JOIN project proi ON proi.id = oi.project_id

WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id

$query .= " AND ct.business_id = ?";

push(@values, $form->{"business_id"});

}

$query .= " AND o.${vc}_id = ?";

push(@values, $form->{"${vc}_id"});

$query .= " AND ct.name ILIKE ?";

$query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";

|| ($vc eq 'vendor' && ($main::auth->assert('purchase_all_edit', 1) || $main::auth->assert('purchase_order_view', 1))) ) ) {

$query .= " AND o.employee_id = ?";

push @values, conv_i($form->{employee_id});

}

$query .= " AND o.salesman_id = ?";

push @values, conv_i($form->{salesman_id});

}

$query .= " AND o.id = 0";

} elsif (!($form->{open} && $form->{closed})) {

$query .= ($form->{open}) ? " AND o.closed = '0'" : " AND o.closed = '1'";

}

($form->{"notdelivered"} ne $form->{"delivered"})) {

$query .= $form->{"delivered"} ?

" AND o.delivered " : " AND NOT o.delivered";

}

$query .= qq| AND o.cusordnumber ILIKE ?|;

$query .= qq| AND o.transdate >= ?|;

push(@values, conv_date($form->{transdatefrom}));

}

$query .= qq| AND o.transdate <= ?|;

push(@values, conv_date($form->{transdateto}));

}

$query .= qq| AND o.reqdate >= ?|;

push(@values, conv_date($form->{reqdatefrom}));

}

if($form->{reqdateto}) {

$query .= qq| AND o.reqdate <= ?|;

push(@values, conv_date($form->{reqdateto}));

}

$query .= qq| AND o.itime::DATE >= ?|;

push(@values, conv_date($form->{insertdatefrom}));

}

if($form->{insertdateto}) {

$query .= qq| AND o.itime::DATE <= ?|;

push(@values, conv_date($form->{insertdateto}));

}

$query .= qq| AND o.shippingpoint ILIKE ?|;

if ($form->{taxzone_id} ne '') { # taxzone_id could be 0

$query .= qq| AND tz.id = ?|;

push(@values, $form->{taxzone_id});

}

$query .= qq| AND o.transaction_description ILIKE ?|;

}

$query .= qq| AND ((o.reqdate IS NULL) OR (o.reqdate < date_trunc('month', current_date)))|;

}

my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>=';

$query .= qq| AND (o.order_probability ${op} ?)|;

if ($form->{expected_billing_date_from}) {

$query .= qq| AND (o.expected_billing_date >= ?)|;

push @values, conv_date($form->{expected_billing_date_from});

}

if ($form->{expected_billing_date_to}) {

$query .= qq| AND (o.expected_billing_date <= ?)|;

push @values, conv_date($form->{expected_billing_date_to});

}

$query .= qq| AND o.intnotes ILIKE ?|;

push(@values, like($form->{intnotes}));

}

$query .= qq| AND o.order_status_id = ?|;

push(@values, $form->{order_status_id});

}

}

if ($form->{fulltext}) {

my @fulltext_fields = qw(o.notes

o.intnotes

o.shippingpoint

o.shipvia

o.transaction_description

o.quonumber

o.ordnumber

o.cusordnumber);

$query .= ' AND (';

$query .= join ' ILIKE ? OR ', @fulltext_fields;

$query .= ' ILIKE ?';

$query .= <<SQL;

OR EXISTS (

SELECT files.id FROM files LEFT JOIN file_full_texts ON (file_full_texts.file_id = files.id)

WHERE files.object_id = o.id AND files.object_type = 'sales_order'

AND file_full_texts.full_text ILIKE ?)

SQL

$query .= <<SQL;

OR EXISTS (

SELECT notes.id FROM notes

WHERE notes.trans_id = o.id AND notes.trans_module LIKE 'oe'

$query .= <<SQL;

OR EXISTS (

SELECT follow_up_links.id FROM follow_up_links

WHERE follow_up_links.trans_id = o.id AND trans_type = 'sales_order'

AND EXISTS (

SELECT notes.id FROM notes

WHERE trans_module LIKE 'fu' AND trans_id = follow_up_links.follow_up_id

AND (notes.subject ILIKE ? OR notes.body ILIKE ?)))

SQL

$query .= <<SQL;

AND EXISTS (

SELECT orderitems.trans_id

FROM orderitems

LEFT JOIN parts ON (orderitems.parts_id = parts.id)

WHERE (orderitems.trans_id = o.id)

AND (parts.partnumber ILIKE ?)

LIMIT 1

)

SQL

push @values, like($form->{parts_partnumber});

}

if ($form->{parts_description}) {

$query .= <<SQL;

AND EXISTS (

SELECT orderitems.trans_id

FROM orderitems

WHERE (orderitems.trans_id = o.id)

AND (orderitems.description ILIKE ?)

LIMIT 1

)

SQL

push @values, like($form->{parts_description});

}

my @tokens = parse_line('\s+', 0, $form->{all});

# ordnumber quonumber customer.name vendor.name transaction_description

$query .= qq| AND (

o.ordnumber ILIKE ? OR

o.quonumber ILIKE ? OR

ct.name ILIKE ? OR

o.transaction_description ILIKE ?

)| for @tokens;

'trans_id_field' => 'ct.id',

'filter' => $form,

);

if ($cvar_where) {

$query .= qq| AND ($cvar_where)|;

push @values, @cvar_values;

}

"transdate" => "o.transdate",

"reqdate" => "o.reqdate",

"id" => "o.id",

"ordnumber" => "o.ordnumber",

"name" => "ct.name",

"employee" => "e.name",

"shippingpoint" => "o.shippingpoint",

$query .= qq| ORDER by | . $sortorder;

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

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

$ref->{billed_netamount} = $billed_netamount{$ref->{id}};

$ref->{remaining_amount} = $ref->{amount} + $ref->{billed_amount};

$ref->{remaining_netamount} = $ref->{netamount} + $ref->{billed_netamount};

} else {

$ref->{remaining_amount} = $ref->{amount} - $ref->{billed_amount};

$ref->{remaining_netamount} = $ref->{netamount} - $ref->{billed_netamount};

}

$ref->{exchangerate} ||= 1;

$id{ $ref->{id} } = $ref->{id};

}

$sth->finish;

my ($items_query, $items_sth);

if ($form->{l_items}) {

$items_query =

qq|SELECT id

FROM orderitems

WHERE trans_id = ?

ORDER BY position|;

$items_sth = prepare_query($form, $dbh, $items_query);

}

foreach my $oe (@{ $form->{OE} }) {

do_statement($form, $items_sth, $items_query, $oe->{id});

$oe->{item_ids} = $dbh->selectcol_arrayref($items_sth);

$oe->{item_ids} = undef if !@{$oe->{item_ids}};

}

$items_sth->finish();

}

}

$main::lxdebug->enter_sub();

my %params = @_;

my $form = $main::form;

my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});

$sales_purchase_filter .= $params{sales} ? qq| OR customer_id IS NOT NULL| : '';

$sales_purchase_filter .= $params{purchase} ? qq| OR vendor_id IS NOT NULL| : '';

$sales_purchase_filter .= ')';

FROM oe

LEFT JOIN customer c ON (oe.customer_id = c.id)

WHERE (COALESCE(quotation, FALSE) = TRUE)

AND (COALESCE(closed, FALSE) = FALSE)

AND ((oe.employee_id = ?) OR (oe.salesman_id = ?))

AND NOT (oe.reqdate ISNULL)

AND (oe.reqdate < current_date)

my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id);

$main::lxdebug->leave_sub();

return $quotations;

}

$main::lxdebug->enter_sub();

my $rc = SL::DB->client->with_transaction(\&_save, $self, $myconfig, $form);

$::lxdebug->leave_sub;

return $rc;

}

sub _save {

my ($self, $myconfig, $form) = @_;