# 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
-# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
+# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
+# MA 02110-1335, USA.
#======================================================================
#
# backend code for customers and vendors
package CT;
-sub get_tuple {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
+use SL::Common;
+use SL::CVar;
+use SL::DBUtils;
+use SL::DB;
+use SL::Util qw(trim);
+use Text::ParseWords;
- my $dbh = $form->dbconnect($myconfig);
- my $query = qq|SELECT ct.*, b.id AS business, s.*, cp.*
- FROM $form->{db} ct
- LEFT JOIN business b on ct.business_id = b.id
- LEFT JOIN shipto s on ct.id = s.trans_id
- LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
- WHERE ct.id = $form->{id} order by cp.cp_id limit 1|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+use strict;
- my $ref = $sth->fetchrow_hashref(NAME_lc);
-
- map { $form->{$_} = $ref->{$_} } keys %$ref;
+sub search {
+ $main::lxdebug->enter_sub();
- $sth->finish;
- if ($form->{salesman_id}) {
- my $query = qq|SELECT ct.name AS salesman
- FROM $form->{db} ct
- WHERE ct.id = $form->{salesman_id}|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ my ( $self, $myconfig, $form ) = @_;
- my ($ref) = $sth->fetchrow_array();
+ # connect to database
+ my $dbh = $form->dbconnect($myconfig);
- $form->{salesman} = $ref;
+ my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
+ my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber};
- $sth->finish;
+ my $where = "1 = 1";
+ my @values;
+
+ my %allowed_sort_columns = (
+ "id" => "ct.id",
+ "customernumber" => "ct.customernumber",
+ "vendornumber" => "ct.vendornumber",
+ "name" => "ct.name",
+ "contact" => "ct.contact",
+ "phone" => "ct.phone",
+ "fax" => "ct.fax",
+ "email" => "ct.email",
+ "street" => "ct.street",
+ "taxnumber" => "ct.taxnumber",
+ "business" => "b.description",
+ "invnumber" => "ct.invnumber",
+ "ordnumber" => "ct.ordnumber",
+ "quonumber" => "ct.quonumber",
+ "zipcode" => "ct.zipcode",
+ "city" => "ct.city",
+ "country" => "ct.country",
+ "gln" => "ct.gln",
+ "discount" => "ct.discount",
+ "insertdate" => "ct.itime",
+ "salesman" => "e.name",
+ "payment" => "pt.description",
+ "pricegroup" => "pg.pricegroup",
+ "ustid" => "ct.ustid",
+ "creditlimit" => "ct.creditlimit",
+ "commercial_court" => "ct.commercial_court",
+ );
+
+ $form->{sort} ||= "name";
+ my $sortorder;
+ if ( $join_records ) {
+ # in UNION case order by hash key, e.g. salesman
+ # the UNION created an implicit select around the result
+ $sortorder = $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
+ } else {
+ # in not UNION case order by hash value, e.g. e.name
+ $sortorder = $allowed_sort_columns{$form->{sort}} ? $allowed_sort_columns{$form->{sort}} : "ct.name";
}
+ my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
- # check if it is orphaned
- my $arap = ($form->{db} eq 'customer') ? "ar" : "ap";
- $query = qq|SELECT a.id
- FROM $arap a
- JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id)
- WHERE ct.id = $form->{id}
- UNION
- SELECT a.id
- FROM oe a
- JOIN $form->{db} ct ON (a.$form->{db}_id = ct.id)
- WHERE ct.id = $form->{id}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- unless ($sth->fetchrow_array) {
- $form->{status} = "orphaned";
+ if ($sortorder !~ /(business|creditlimit|id|discount|itime)/ && !$join_records) {
+ $sortorder = "lower($sortorder) ${sortdir}";
+ } else {
+ $sortorder .= " ${sortdir}";
}
- $sth->finish;
- # get tax labels
- $query = qq|SELECT c.accno, c.description
- FROM chart c
- JOIN tax t ON (t.chart_id = c.id)
- WHERE c.link LIKE '%CT_tax%'
- ORDER BY c.accno|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- $form->{taxaccounts} .= "$ref->{accno} ";
- $form->{tax}{ $ref->{accno} }{description} = $ref->{description};
+ if ($form->{"${cv}number"}) {
+ $where .= " AND ct.${cv}number ILIKE ?";
+ push(@values, like($form->{"${cv}number"}));
}
- $sth->finish;
- chop $form->{taxaccounts};
-
- # get taxes for customer/vendor
- $query = qq|SELECT c.accno
- FROM chart c
- JOIN $form->{db}tax t ON (t.chart_id = c.id)
- WHERE t.$form->{db}_id = $form->{id}|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- $form->{tax}{ $ref->{accno} }{taxable} = 1;
- }
- $sth->finish;
- # get business types
- $query = qq|SELECT id, description
- FROM business
- ORDER BY 1|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{all_business} }, $ref;
+ foreach my $key (qw(name contact email)) {
+ if ($form->{$key}) {
+ $where .= " AND ct.$key ILIKE ?";
+ push(@values, like($form->{$key}));
+ }
}
- $sth->finish;
-
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-## LINET
-sub query_titles_and_greetings {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
- my (%tmp, $ref);
-
- my $dbh = $form->dbconnect($myconfig);
- $query =
- "SELECT DISTINCT(c.cp_greeting) FROM contacts c WHERE c.cp_greeting LIKE '%'";
- $sth = $dbh->prepare($query);
- $sth->execute() || $form->dberror($query);
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- next unless ($ref->{cp_greeting} =~ /[a-zA-Z]/);
- $tmp{ $ref->{cp_greeting} } = 1;
+ if ($form->{cp_name}) {
+ $where .= " AND ct.id IN (SELECT cp_cv_id FROM contacts WHERE lower(cp_name) LIKE lower(?))";
+ push @values, like($form->{cp_name});
}
- $sth->finish();
- @{ $form->{GREETINGS} } = sort(keys(%tmp));
-
- %tmp = ();
-
- $query =
- "SELECT DISTINCT(c.cp_title) FROM contacts c WHERE c.cp_title LIKE '%'";
- $sth = $dbh->prepare($query);
- $sth->execute() || $form->dberror($query);
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- next unless ($ref->{cp_title} =~ /[a-zA-Z]/);
- $tmp{ $ref->{cp_title} } = 1;
+ if ($form->{addr_street}) {
+ $where .= qq| AND ((ct.street ILIKE ?) | .
+ qq| OR | .
+ qq| (ct.id IN ( | .
+ qq| SELECT sc.trans_id FROM shipto sc | .
+ qq| WHERE (sc.module = 'CT') | .
+ qq| AND (sc.shiptostreet ILIKE ?) | .
+ qq| ))) |;
+ push @values, (like($form->{addr_street})) x 2;
}
- $sth->finish();
- @{ $form->{TITLES} } = sort(keys(%tmp));
-
- $dbh->disconnect();
- $main::lxdebug->leave_sub();
-}
-## /LINET
+ if ($form->{addr_zipcode}) {
+ $where .= qq| AND ((ct.zipcode ILIKE ?) | .
+ qq| OR | .
+ qq| (ct.id IN ( | .
+ qq| SELECT sc.trans_id FROM shipto sc | .
+ qq| WHERE (sc.module = 'CT') | .
+ qq| AND (sc.shiptozipcode ILIKE ?) | .
+ qq| ))) |;
+ push @values, (like($form->{addr_zipcode})) x 2;
+ }
-sub taxaccounts {
- $main::lxdebug->enter_sub();
+ if ($form->{addr_city}) {
+ $where .= " AND ((lower(ct.city) LIKE lower(?))
+ OR
+ (ct.id IN (
+ SELECT sc.trans_id
+ FROM shipto sc
+ WHERE (sc.module = 'CT')
+ AND (lower(sc.shiptocity) LIKE lower(?))
+ ))
+ )";
+ push @values, (like($form->{addr_city})) x 2;
+ }
- my ($self, $myconfig, $form) = @_;
+ if ($form->{addr_country}) {
+ $where .= " AND ((lower(ct.country) LIKE lower(?))
+ OR
+ (ct.id IN (
+ SELECT so.trans_id
+ FROM shipto so
+ WHERE (so.module = 'CT')
+ AND (lower(so.shiptocountry) LIKE lower(?))
+ ))
+ )";
+ push @values, (like($form->{addr_country})) x 2;
+ }
- my $dbh = $form->dbconnect($myconfig);
+ if ($form->{addr_gln}) {
+ $where .= " AND ((lower(ct.gln) LIKE lower(?))
+ OR
+ (ct.id IN (
+ SELECT so.trans_id
+ FROM shipto so
+ WHERE (so.module = 'CT')
+ AND (lower(so.shiptogln) LIKE lower(?))
+ ))
+ )";
+ push @values, (like($form->{addr_gln})) x 2;
+ }
- # get tax labels
- my $query = qq|SELECT accno, description
- FROM chart c, tax t
- WHERE c.link LIKE '%CT_tax%'
- AND c.id = t.chart_id
- ORDER BY accno|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- my $ref = ();
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- $form->{taxaccounts} .= "$ref->{accno} ";
- $form->{tax}{ $ref->{accno} }{description} = $ref->{description};
+ if ( $form->{status} eq 'orphaned' ) {
+ $where .=
+ qq| AND ct.id NOT IN | .
+ qq| (SELECT o.${cv}_id FROM oe o, $cv cv WHERE cv.id = o.${cv}_id)|;
+ if ($cv eq 'customer') {
+ $where .=
+ qq| AND ct.id NOT IN | .
+ qq| (SELECT a.customer_id FROM ar a, customer cv | .
+ qq| WHERE cv.id = a.customer_id)|;
+ }
+ if ($cv eq 'vendor') {
+ $where .=
+ qq| AND ct.id NOT IN | .
+ qq| (SELECT a.vendor_id FROM ap a, vendor cv | .
+ qq| WHERE cv.id = a.vendor_id)|;
+ }
+ $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
}
- $sth->finish;
- chop $form->{taxaccounts};
- # this is just for the selection for type of business
- $query = qq|SELECT id, description
- FROM business|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ if ($form->{obsolete} eq "Y") {
+ $where .= qq| AND ct.obsolete|;
+ } elsif ($form->{obsolete} eq "N") {
+ $where .= qq| AND NOT ct.obsolete|;
+ }
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{all_business} }, $ref;
+ if ($form->{business_id}) {
+ $where .= qq| AND (ct.business_id = ?)|;
+ push(@values, conv_i($form->{business_id}));
}
- $sth->finish;
- $dbh->disconnect;
+ if ($form->{salesman_id}) {
+ $where .= qq| AND (ct.salesman_id = ?)|;
+ push(@values, conv_i($form->{salesman_id}));
+ }
- $main::lxdebug->leave_sub();
-}
+ if($form->{insertdatefrom}) {
+ $where .= qq| AND (ct.itime::DATE >= ?)|;
+ push@values, conv_date($form->{insertdatefrom});
+ }
-sub save_customer {
- $main::lxdebug->enter_sub();
+ if($form->{insertdateto}) {
+ $where .= qq| AND (ct.itime::DATE <= ?)|;
+ push @values, conv_date($form->{insertdateto});
+ }
- my ($self, $myconfig, $form) = @_;
+ if ($form->{all}) {
+ my @tokens = parse_line('\s+', 0, $form->{all});
+ $where .= qq| AND (
+ ct.${cv}number ILIKE ? OR
+ ct.name ILIKE ?
+ )| for @tokens;
+ push @values, ("%$_%")x2 for @tokens;
+ }
- # set pricegroup to default
- if ($form->{klass}) { }
- else { $form->{klass} = 0; }
+ if (($form->{create_zugferd_invoices} // '') ne '') {
+ $where .= qq| AND (ct.create_zugferd_invoices = ?)|;
+ push @values, $form->{create_zugferd_invoices};
+ }
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-##LINET
- map({
- $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
- if ($form->{"selected_cp_${_}"});
- } qw(title greeting));
-#
- # escape '
- map { $form->{$_} =~ s/\'/\'\'/g }
- qw(customernumber name street zipcode city country homepage contact notes cp_title cp_greeting language pricegroup);
-##/LINET
- # assign value discount, terms, creditlimit
- $form->{discount} = $form->parse_amount($myconfig, $form->{discount});
- $form->{discount} /= 100;
- $form->{terms} *= 1;
- $form->{taxincluded} *= 1;
- $form->{obsolete} *= 1;
- $form->{business} *= 1;
- $form->{salesman_id} *= 1;
- $form->{creditlimit} = $form->parse_amount($myconfig, $form->{creditlimit});
-
- my ($query, $sth);
-
- if ($form->{id}) {
- $query = qq|DELETE FROM customertax
- WHERE customer_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
- $query = qq|DELETE FROM shipto
- WHERE trans_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
- } else {
- my $uid = rand() . time;
+ if ($form->{all_phonenumbers}) {
+ $where .= qq| AND (ct.phone ILIKE ? OR
+ ct.fax ILIKE ? OR
+ ct.id IN
+ (SELECT cp_cv_id FROM contacts
+ WHERE cp_phone1 ILIKE ? OR
+ cp_phone2 ILIKE ? OR
+ cp_fax ILIKE ? OR
+ cp_mobile1 ILIKE ? OR
+ cp_mobile2 ILIKE ? OR
+ cp_satphone ILIKE ? OR
+ cp_satfax ILIKE ? OR
+ cp_privatphone ILIKE ?
+ )
+ )|;
+ push @values, (like(trim($form->{all_phonenumbers})))x10;
+ }
- $uid .= $form->{login};
+ my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
+ 'trans_id_field' => 'ct.id',
+ 'filter' => $form);
- $uid = substr($uid, 2, 75);
+ if ($cvar_where) {
+ $where .= qq| AND ($cvar_where)|;
+ push @values, @cvar_values;
+ }
- $query = qq|INSERT INTO customer (name)
- VALUES ('$uid')|;
- $dbh->do($query) || $form->dberror($query);
+ my $pg_select = $form->{l_pricegroup} ? qq|, pg.pricegroup as pricegroup | : '';
+ my $pg_join = $form->{l_pricegroup} ? qq|LEFT JOIN pricegroup pg ON (ct.pricegroup_id = pg.id) | : '';
- $query = qq|SELECT c.id FROM customer c
- WHERE c.name = '$uid'|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ my $main_cp_select = '';
+ if ($form->{l_main_contact_person}) {
+ $main_cp_select = qq/, (SELECT concat(cp.cp_givenname, ' ', cp.cp_name, ' | ', cp.cp_email, ' | ', cp.cp_phone1)
+ FROM contacts cp WHERE ct.id=cp.cp_cv_id AND cp.cp_main LIMIT 1)
+ AS main_contact_person /;
+ }
+ my $query =
+ qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
+ qq| pt.description as payment | .
+ $pg_select .
+ $main_cp_select .
+ (qq|, NULL AS invnumber, NULL AS ordnumber, NULL AS quonumber, NULL AS invid, NULL AS module, NULL AS formtype, NULL AS closed | x!! $join_records) .
+ qq|FROM $cv ct | .
+ qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
+ qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
+ qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
+ $pg_join .
+ qq|WHERE $where|;
+
+ my @saved_values = @values;
+ # redo for invoices, orders and quotations
+ if ($join_records) {
+ my $union = "UNION";
- ($form->{id}) = $sth->fetchrow_array;
- $sth->finish;
- if (!$form->{customernumber} && $form->{business}) {
- $form->{customernumber} =
- $form->update_business($myconfig, $form->{business});
- }
- if (!$form->{customernumber}) {
- $form->{customernumber} =
- $form->update_defaults($myconfig, "customernumber");
+ if ($form->{l_invnumber}) {
+ my $ar = $cv eq 'customer' ? 'ar' : 'ap';
+ my $module = $ar eq 'ar' ? 'is' : 'ir';
+ push(@values, @saved_values);
+ $query .=
+ qq| UNION | .
+ qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
+ qq| pt.description as payment | .
+ $pg_select .
+ $main_cp_select .
+ qq|, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | .
+ qq| '$module' AS module, 'invoice' AS formtype, | .
+ qq| (a.amount = a.paid) AS closed | .
+ qq|FROM $cv ct | .
+ qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
+ qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
+ qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
+ qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
+ $pg_join .
+ qq|WHERE $where AND (a.invoice = '1')|;
}
- }
+ if ( $form->{l_ordnumber} ) {
+ push(@values, @saved_values);
+ $query .=
+ qq| UNION | .
+ qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
+ qq| pt.description as payment | .
+ $pg_select .
+ $main_cp_select .
+ qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
+ qq| 'oe' AS module, 'order' AS formtype, o.closed | .
+ qq|FROM $cv ct | .
+ qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
+ qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
+ qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
+ qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
+ $pg_join .
+ qq|WHERE $where AND (o.quotation = '0')|;
+ }
- $query = qq|UPDATE customer SET
- customernumber = '$form->{customernumber}',
- name = '$form->{name}',
- department_1 = '$form->{department_1}',
- department_2 = '$form->{department_2}',
- street = '$form->{street}',
- zipcode = '$form->{zipcode}',
- city = '$form->{city}',
- country = '$form->{country}',
- homepage = '$form->{homepage}',
- contact = '$form->{contact}',
- phone = '$form->{phone}',
- fax = '$form->{fax}',
- email = '$form->{email}',
- cc = '$form->{cc}',
- bcc = '$form->{bcc}',
- notes = '$form->{notes}',
- discount = $form->{discount},
- creditlimit = $form->{creditlimit},
- terms = $form->{terms},
- taxincluded = '$form->{taxincluded}',
- business_id = $form->{business},
- taxnumber = '$form->{taxnumber}',
- sic_code = '$form->{sic}',
- language = '$form->{language}',
- account_number = '$form->{account_number}',
- bank_code = '$form->{bank_code}',
- bank = '$form->{bank}',
- obsolete = '$form->{obsolete}',
- ustid = '$form->{ustid}',
- username = '$form->{username}',
- salesman_id = '$form->{salesman_id}',
- user_password = '$form->{user_password}',
- c_vendor_id = '$form->{c_vendor_id}',
- klass = '$form->{klass}'
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
- if ($form->{cp_id}) {
- $query = qq|UPDATE contacts SET
- cp_greeting = '$form->{cp_greeting}',
- cp_title = '$form->{cp_title}',
- cp_givenname = '$form->{cp_givenname}',
- cp_name = '$form->{cp_name}',
- cp_email = '$form->{cp_email}',
- cp_phone1 = '$form->{cp_phone1}',
- cp_phone2 = '$form->{cp_phone2}'
- WHERE cp_id = $form->{cp_id}|;
- } elsif ($form->{cp_name} || $form->{cp_givenname}) {
- $query =
- qq|INSERT INTO contacts ( cp_cv_id, cp_greeting, cp_title, cp_givenname, cp_name, cp_email, cp_phone1, cp_phone2)
- VALUES ($form->{id}, '$form->{cp_greeting}','$form->{cp_title}','$form->{cp_givenname}','$form->{cp_name}','$form->{cp_email}','$form->{cp_phone1}','$form->{cp_phone2}')|;
- }
- $dbh->do($query) || $form->dberror($query);
-
- # save taxes
- foreach $item (split / /, $form->{taxaccounts}) {
- if ($form->{"tax_$item"}) {
- $query = qq|INSERT INTO customertax (customer_id, chart_id)
- VALUES ($form->{id}, (SELECT c.id
- FROM chart c
- WHERE c.accno = '$item'))|;
- $dbh->do($query) || $form->dberror($query);
+ if ( $form->{l_quonumber} ) {
+ push(@values, @saved_values);
+ $query .=
+ qq| UNION | .
+ qq|SELECT ct.*, ct.itime::DATE AS insertdate, b.description AS business, e.name as salesman, | .
+ qq| pt.description as payment | .
+ $pg_select .
+ $main_cp_select .
+ qq|, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | .
+ qq| 'oe' AS module, 'quotation' AS formtype, o.closed | .
+ qq|FROM $cv ct | .
+ qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
+ qq|LEFT JOIN business b ON (ct.business_id = b.id) | .
+ qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | .
+ qq|LEFT JOIN payment_terms pt ON (ct.payment_id = pt.id) | .
+ $pg_join .
+ qq|WHERE $where AND (o.quotation = '1')|;
}
}
- # add shipto
- $form->add_shipto($dbh, $form->{id});
+ $query .= qq| ORDER BY $sortorder|;
- $dbh->disconnect;
+ $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values);
$main::lxdebug->leave_sub();
}
-sub save_vendor {
+sub get_contact {
$main::lxdebug->enter_sub();
- my ($self, $myconfig, $form) = @_;
+ my ( $self, $myconfig, $form ) = @_;
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-##LINET
- map({
- $form->{"cp_${_}"} = $form->{"selected_cp_${_}"}
- if ($form->{"selected_cp_${_}"});
- } qw(title greeting));
-
- # escape '
- map { $form->{$_} =~ s/\'/\'\'/g }
- qw(vendornumber name street zipcode city country homepage contact notes cp_title cp_greeting language);
-##/LINET
- $form->{discount} = $form->parse_amount($myconfig, $form->{discount});
- $form->{discount} /= 100;
- $form->{terms} *= 1;
- $form->{taxincluded} *= 1;
- $form->{obsolete} *= 1;
- $form->{business} *= 1;
- $form->{creditlimit} = $form->parse_amount($myconfig, $form->{creditlimit});
-
- my $query;
-
- if ($form->{id}) {
- $query = qq|DELETE FROM vendortax
- WHERE vendor_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
- $query = qq|DELETE FROM shipto
- WHERE trans_id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
- } else {
- my $uid = time;
- $uid .= $form->{login};
- my $uid = rand() . time;
- $uid .= $form->{login};
- $uid = substr($uid, 2, 75);
- $query = qq|INSERT INTO vendor (name)
- VALUES ('$uid')|;
- $dbh->do($query) || $form->dberror($query);
-
- $query = qq|SELECT v.id FROM vendor v
- WHERE v.name = '$uid'|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- ($form->{id}) = $sth->fetchrow_array;
- $sth->finish;
- if (!$form->{vendornumber}) {
- $form->{vendornumber} =
- $form->update_defaults($myconfig, "vendornumber");
- }
+ die 'Missing argument: cp_id' unless $::form->{cp_id};
- }
+ my $dbh = SL::DB->client->dbh;
+ my $query =
+ qq|SELECT * FROM contacts c | .
+ qq|WHERE cp_id = ? ORDER BY cp_id limit 1|;
+ my $sth = prepare_execute_query($form, $dbh, $query, $form->{cp_id});
+ my $ref = $sth->fetchrow_hashref("NAME_lc");
-##LINET
- $query = qq|UPDATE vendor SET
- vendornumber = '$form->{vendornumber}',
- name = '$form->{name}',
- department_1 = '$form->{department_1}',
- department_2 = '$form->{department_2}',
- street = '$form->{street}',
- zipcode = '$form->{zipcode}',
- city = '$form->{city}',
- country = '$form->{country}',
- homepage = '$form->{homepage}',
- contact = '$form->{contact}',
- phone = '$form->{phone}',
- fax = '$form->{fax}',
- email = '$form->{email}',
- cc = '$form->{cc}',
- bcc = '$form->{bcc}',
- notes = '$form->{notes}',
- terms = $form->{terms},
- discount = $form->{discount},
- creditlimit = $form->{creditlimit},
- taxincluded = '$form->{taxincluded}',
- gifi_accno = '$form->{gifi_accno}',
- business_id = $form->{business},
- taxnumber = '$form->{taxnumber}',
- sic_code = '$form->{sic}',
- language = '$form->{language}',
- account_number = '$form->{account_number}',
- bank_code = '$form->{bank_code}',
- bank = '$form->{bank}',
- obsolete = '$form->{obsolete}',
- ustid = '$form->{ustid}',
- username = '$form->{username}',
- user_password = '$form->{user_password}',
- v_customer_id = '$form->{v_customer_id}'
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
-
- if ($form->{cp_id}) {
- $query = qq|UPDATE contacts SET
- cp_greeting = '$form->{cp_greeting}',
- cp_title = '$form->{cp_title}',
- cp_givenname = '$form->{cp_givenname}',
- cp_name = '$form->{cp_name}',
- cp_email = '$form->{cp_email}',
- cp_phone1 = '$form->{cp_phone1}',
- cp_phone2 = '$form->{cp_phone2}'
- WHERE cp_id = $form->{cp_id}|;
- } elsif ($form->{cp_name} || $form->{cp_givenname}) {
- $query =
- qq|INSERT INTO contacts ( cp_cv_id, cp_greeting, cp_title, cp_givenname, cp_name, cp_email, cp_phone1, cp_phone2)
- VALUES ($form->{id}, '$form->{cp_greeting}','$form->{cp_title}','$form->{cp_givenname}','$form->{cp_name}','$form->{cp_email}','$form->{cp_phone1}','$form->{cp_phone2}')|;
- }
- $dbh->do($query) || $form->dberror($query);
-
- # save taxes
- foreach $item (split / /, $form->{taxaccounts}) {
- if ($form->{"tax_$item"}) {
- $query = qq|INSERT INTO vendortax (vendor_id, chart_id)
- VALUES ($form->{id}, (SELECT c.id
- FROM chart c
- WHERE c.accno = '$item'))|;
- $dbh->do($query) || $form->dberror($query);
- }
- }
+ map { $form->{$_} = $ref->{$_} } keys %$ref;
- # add shipto
- $form->add_shipto($dbh, $form->{id});
+ $query = qq|SELECT COUNT(cp_id) AS used FROM (
+ SELECT cp_id FROM oe UNION
+ SELECT cp_id FROM ar UNION
+ SELECT cp_id FROM ap UNION
+ SELECT cp_id FROM delivery_orders
+ ) AS cpid WHERE cp_id = ? OR ? = 0|;
+ ($form->{cp_used}) = selectfirst_array_query($form, $dbh, $query, ($form->{cp_id})x2);
- $dbh->disconnect;
+ $sth->finish;
$main::lxdebug->leave_sub();
}
-sub delete {
+sub get_bank_info {
$main::lxdebug->enter_sub();
- my ($self, $myconfig, $form) = @_;
+ my $self = shift;
+ my %params = @_;
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
+ Common::check_params(\%params, qw(vc id));
+
+ my $myconfig = \%main::myconfig;
+ my $form = $main::form;
+
+ my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
- # delete vendor
- my $query = qq|DELETE FROM $form->{db}
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
+ my $table = $params{vc} eq 'customer' ? 'customer' : 'vendor';
+ my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
+ my $placeholders = join ", ", ('?') x scalar @ids;
+ my $c_mandate = $params{vc} eq 'customer' ? ', mandator_id, mandate_date_of_signature' : '';
+ my $query = qq|SELECT id, name, account_number, bank, bank_code, iban, bic ${c_mandate}
+ FROM ${table}
+ WHERE id IN (${placeholders})|;
- $dbh->disconnect;
+ my $result = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
+
+ if (ref $params{id} eq 'ARRAY') {
+ $result = { map { $_->{id} => $_ } @{ $result } };
+ } else {
+ $result = $result->[0] || { 'id' => $params{id} };
+ }
$main::lxdebug->leave_sub();
+
+ return $result;
}
-sub search {
- $main::lxdebug->enter_sub();
+sub search_contacts {
+ $::lxdebug->enter_sub;
- my ($self, $myconfig, $form) = @_;
+ my $self = shift;
+ my %params = @_;
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = $params{dbh} || $::form->get_standard_dbh;
- my $where = "1 = 1";
- $form->{sort} = "name" unless ($form->{sort});
+ my %sortspecs = (
+ 'cp_name' => 'cp_name, cp_givenname',
+ 'vcname' => 'vcname, cp_name, cp_givenname',
+ 'vcnumber' => 'vcnumber, cp_name, cp_givenname',
+ );
- if ($form->{"$form->{db}number"}) {
- my $companynumber = $form->like(lc $form->{"$form->{db}number"});
- $where .= " AND lower(ct.$form->{db}number) LIKE '$companynumber'";
- }
- if ($form->{name}) {
- my $name = $form->like(lc $form->{name});
- $where .= " AND lower(ct.name) LIKE '$name'";
- }
- if ($form->{contact}) {
- my $contact = $form->like(lc $form->{contact});
- $where .= " AND lower(ct.contact) LIKE '$contact'";
- }
- if ($form->{email}) {
- my $email = $form->like(lc $form->{email});
- $where .= " AND lower(ct.email) LIKE '$email'";
- }
+ my %sortcols = map { $_ => 1 } qw(cp_name cp_givenname cp_phone1 cp_phone2 cp_mobile1 cp_email cp_street cp_zipcode cp_city cp_position vcname vcnumber);
- if ($form->{status} eq 'orphaned') {
- $where .= qq| AND ct.id NOT IN (SELECT o.$form->{db}_id
- FROM oe o, $form->{db} cv
- WHERE cv.id = o.$form->{db}_id)|;
- if ($form->{db} eq 'customer') {
- $where .= qq| AND ct.id NOT IN (SELECT a.customer_id
- FROM ar a, customer cv
- WHERE cv.id = a.customer_id)|;
- }
- if ($form->{db} eq 'vendor') {
- $where .= qq| AND ct.id NOT IN (SELECT a.vendor_id
- FROM ap a, vendor cv
- WHERE cv.id = a.vendor_id)|;
- }
- $form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
- }
+ my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name';
+ $::form->{sort} = $order_by;
+ $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by});
- my $query = qq|SELECT ct.*, b.description AS business
- FROM $form->{db} ct
- LEFT JOIN business b ON (ct.business_id = b.id)
- WHERE $where|;
+ my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC';
+ $order_by =~ s/,/ ${sortdir},/g;
+ $order_by .= " $sortdir";
- # redo for invoices, orders and quotations
- if ($form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber}) {
+ my @where_tokens = ();
+ my @values;
- my ($ar, $union, $module);
- $query = "";
+ if ($params{search_term}) {
+ my @tokens;
+ push @tokens,
+ 'cp.cp_name ILIKE ?',
+ 'cp.cp_givenname ILIKE ?',
+ 'cp.cp_email ILIKE ?';
+ push @values, (like($params{search_term})) x 3;
- if ($form->{l_invnumber}) {
- $ar = ($form->{db} eq 'customer') ? 'ar' : 'ap';
- $module = ($ar eq 'ar') ? 'is' : 'ir';
-
- $query = qq|SELECT ct.*, b.description AS business,
- a.invnumber, a.ordnumber, a.quonumber, a.id AS invid,
- '$module' AS module, 'invoice' AS formtype,
- (a.amount = a.paid) AS closed
- FROM $form->{db} ct
- JOIN $ar a ON (a.$form->{db}_id = ct.id)
- LEFT JOIN business b ON (ct.business_id = b.id)
- WHERE $where
- AND a.invoice = '1'|;
-
- $union = qq|
- UNION|;
+ if (($params{search_term} =~ m/\d/) && ($params{search_term} !~ m/[^\d \(\)+\-]/)) {
+ my $number = $params{search_term};
+ $number =~ s/[^\d]//g;
+ $number = join '[ /\(\)+\-]*', split(m//, $number);
+ push @tokens, map { "($_ ~ '$number')" } qw(cp_phone1 cp_phone2 cp_mobile1 cp_mobile2);
}
- if ($form->{l_ordnumber}) {
- $query .= qq|$union
- SELECT ct.*, b.description AS business,
- ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid,
- 'oe' AS module, 'order' AS formtype,
- o.closed
- FROM $form->{db} ct
- JOIN oe o ON (o.$form->{db}_id = ct.id)
- LEFT JOIN business b ON (ct.business_id = b.id)
- WHERE $where
- AND o.quotation = '0'|;
-
- $union = qq|
- UNION|;
- }
+ push @where_tokens, map { "($_)" } join ' OR ', @tokens;
+ }
- if ($form->{l_quonumber}) {
- $query .= qq|$union
- SELECT ct.*, b.description AS business,
- ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid,
- 'oe' AS module, 'quotation' AS formtype,
- o.closed
- FROM $form->{db} ct
- JOIN oe o ON (o.$form->{db}_id = ct.id)
- LEFT JOIN business b ON (ct.business_id = b.id)
- WHERE $where
- AND o.quotation = '1'|;
+ my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'Contacts',
+ 'trans_id_field' => 'cp.cp_id',
+ 'filter' => $params{filter});
- }
+ if ($cvar_where) {
+ push @where_tokens, $cvar_where;
+ push @values, @cvar_values;
}
- $query .= qq|
- ORDER BY $form->{sort}|;
+ if (my $filter = $params{filter}) {
+ for (qw(name title givenname email project abteilung)) {
+ next unless $filter->{"cp_$_"};
+ add_token(\@where_tokens, \@values, col => "cp.cp_$_", val => $filter->{"cp_$_"}, method => 'ILIKE', esc => 'substr');
+ }
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-##LINET
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- $ref->{address} = "";
- map { $ref->{address} .= "$ref->{$_} "; } qw(street zipcode city country);
- push @{ $form->{CT} }, $ref;
+ push @where_tokens, 'cp.cp_cv_id IS NOT NULL' if $filter->{status} eq 'active';
+ push @where_tokens, 'cp.cp_cv_id IS NULL' if $filter->{status} eq 'orphaned';
}
-##/LINET
- $sth->finish;
- $dbh->disconnect;
- $main::lxdebug->leave_sub();
+ my $where = @where_tokens ? 'WHERE ' . join ' AND ', @where_tokens : '';
+
+ my $query = qq|SELECT cp.*,
+ COALESCE(c.id, v.id) AS vcid,
+ COALESCE(c.name, v.name) AS vcname,
+ COALESCE(c.customernumber, v.vendornumber) AS vcnumber,
+ CASE WHEN c.name IS NULL THEN 'vendor' ELSE 'customer' END AS db
+ FROM contacts cp
+ LEFT JOIN customer c ON (cp.cp_cv_id = c.id)
+ LEFT JOIN vendor v ON (cp.cp_cv_id = v.id)
+ $where
+ ORDER BY $order_by|;
+
+ my $contacts = selectall_hashref_query($::form, $dbh, $query, @values);
+
+ $::lxdebug->leave_sub;
+
+ return @{ $contacts };
}
-1;
+1;
projects / kivitendo-erp.git / blobdiff