X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCT.pm;h=b52c8794acafa55317b3c4bb674b4eab08d223c9;hb=96e32f352f3937bdd2deb279a55d94871396d9c0;hp=e5ee4af82af93e5d3691456d01070bbd09b8fed2;hpb=0495d62b117ad19e83cecaa9ca251dfa31907d98;p=kivitendo-erp.git diff --git a/SL/CT.pm b/SL/CT.pm index e5ee4af82..b52c8794a 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -25,7 +25,8 @@ # 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 @@ -36,604 +37,153 @@ #====================================================================== package CT; -use Data::Dumper; -use SL::DBUtils; - -sub get_tuple { - $main::lxdebug->enter_sub(); - - my ( $self, $myconfig, $form ) = @_; - - my $cv = $form->{db} eq "customer" ? "customer" : "vendor"; - - my $dbh = $form->dbconnect($myconfig); - my $query = - qq|SELECT ct.*, b.id AS business, cp.* | . - qq|FROM $cv ct | . - qq|LEFT JOIN business b ON (ct.business_id = b.id) | . - qq|LEFT JOIN contacts cp ON (ct.id = cp.cp_cv_id) | . - qq|WHERE (ct.id = ?) | . - qq|ORDER BY cp.cp_id LIMIT 1|; - my $sth = prepare_execute_query($form, $dbh, $query, $form->{id}); - - my $ref = $sth->fetchrow_hashref(NAME_lc); - - map { $form->{$_} = $ref->{$_} } keys %$ref; - - $sth->finish; - if ( $form->{salesman_id} ) { - my $query = - qq|SELECT ct.name AS salesman | . - qq|FROM $cv ct | . - qq|WHERE ct.id = ?|; - ($form->{salesman}) = - selectrow_query($form, $dbh, $query, $form->{salesman_id}); - } - - # check if it is orphaned - my $arap = ( $form->{db} eq 'customer' ) ? "ar" : "ap"; - $query = - qq|SELECT a.id | . - qq|FROM $arap a | . - qq|JOIN $cv ct ON (a.${cv}_id = ct.id) | . - qq|WHERE ct.id = ? | . - qq|UNION | . - qq|SELECT a.id | . - qq|FROM oe a | . - qq|JOIN $cv ct ON (a.${cv}_id = ct.id) | . - qq|WHERE ct.id = ?|; - my ($dummy) = selectrow_query($form, $dbh, $query, $form->{id}, $form->{id}); - $form->{status} = "orphaned" unless ($dummy); - - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub populate_drop_down_boxes { - $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form, $provided_dbh) = @_; - - my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect($myconfig); - - # get business types - $query = qq|SELECT id, description FROM business ORDER BY id|; - $form->{all_business} = selectall_hashref_query($form, $dbh, $query); - - # get shipto address - $query = - qq|SELECT shipto_id, shiptoname, shiptodepartment_1 | . - qq|FROM shipto WHERE (trans_id = ?) AND (module = 'CT')|; - $form->{SHIPTO} = selectall_hashref_query($form, $dbh, $query, $form->{id}); - - # get contacts - $query = qq|SELECT cp_id, cp_name FROM contacts WHERE cp_cv_id = ?|; - $form->{CONTACTS} = selectall_hashref_query($form, $dbh, $query, $form->{id}); - - # get languages - $query = qq|SELECT id, description FROM language ORDER BY id|; - $form->{languages} = selectall_hashref_query($form, $dbh, $query); - - # get payment terms - $query = qq|SELECT id, description FROM payment_terms ORDER BY sortkey|; - $form->{payment_terms} = selectall_hashref_query($form, $dbh, $query); - - $dbh->disconnect() unless ($provided_dbh); - - $main::lxdebug->leave_sub(); -} - -sub query_titles_and_greetings { - $main::lxdebug->enter_sub(); - - my ( $self, $myconfig, $form ) = @_; - my ( %tmp, $ref ); +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); +use strict; - $query = - qq|SELECT DISTINCT(cp_greeting) | . - qq|FROM contacts | . - qq|WHERE cp_greeting ~ '[a-zA-Z]' | . - qq|ORDER BY cp_greeting|; - $form->{GREETINGS} = [ selectall_array_query($form, $dbh, $query) ]; - - $query = - qq|SELECT DISTINCT(greeting) | . - qq|FROM customer | . - qq|WHERE greeting ~ '[a-zA-Z]' | . - qq|UNION | . - qq|SELECT DISTINCT(greeting) | . - qq|FROM vendor | . - qq|WHERE greeting ~ '[a-zA-Z]' | . - qq|ORDER BY greeting|; - my %tmp; - map({ $tmp{$_} = 1; } selectall_array_query($form, $dbh, $query)); - $form->{COMPANY_GREETINGS} = [ sort(keys(%tmp)) ]; - - $query = - qq|SELECT DISTINCT(cp_title) | . - qq|FROM contacts | . - qq|WHERE cp_title ~ '[a-zA-Z]'|; - $form->{TITLES} = [ selectall_array_query($form, $dbh, $query) ]; - - $query = - qq|SELECT DISTINCT(cp_abteilung) | . - qq|FROM contacts | . - qq|WHERE cp_abteilung ~ '[a-zA-Z]'|; - $form->{DEPARTMENT} = [ selectall_array_query($form, $dbh, $query) ]; - - $dbh->disconnect(); - $main::lxdebug->leave_sub(); -} - -sub save_customer { +sub search { $main::lxdebug->enter_sub(); my ( $self, $myconfig, $form ) = @_; - # set pricegroup to default - $form->{klass} = 0 unless ($form->{klass}); - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); - - map( { - $form->{"cp_${_}"} = $form->{"selected_cp_${_}"} - if ( $form->{"selected_cp_${_}"} ); - } qw(title greeting abteilung) ); - $form->{"greeting"} = $form->{"selected_company_greeting"} - if ( $form->{"selected_company_greeting"} ); - - # assign value discount, terms, creditlimit - $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} ); - $form->{discount} /= 100; - $form->{creditlimit} = $form->parse_amount( $myconfig, $form->{creditlimit} ); + my $dbh = $form->dbconnect($myconfig); - my ( $query, $sth, $f_id ); + my $cv = $form->{db} eq "customer" ? "customer" : "vendor"; + my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber}; - if ( $form->{id} ) { - $query = qq|SELECT id FROM customer WHERE customernumber = ?|; - ($f_id) = selectrow_query($form, $dbh, $query, $form->{customernumber}); + my $where = "1 = 1"; + my @values; - if (($f_id ne $form->{id}) && ($f_id ne "")) { - $main::lxdebug->leave_sub(); - return 3; - } + 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 { - if (!$form->{customernumber} && $form->{business}) { - $form->{customernumber} = - $form->update_business($myconfig, $form->{business}, $dbh); - } - if (!$form->{customernumber}) { - $form->{customernumber} = - $form->update_defaults($myconfig, "customernumber", $dbh); - } - - $query = qq|SELECT c.id FROM customer c WHERE c.customernumber = ?|; - ($f_id) = selectrow_query($form, $dbh, $query, $form->{customernumber}); - if ($f_id ne "") { - $main::lxdebug->leave_sub(); - return 3; - } - - $query = qq|SELECT nextval('id')|; - ($form->{id}) = selectrow_query($form, $dbh, $query); - - $query = qq|INSERT INTO customer (id, name) VALUES (?, '')|; - do_query($form, $dbh, $query, $form->{id}); - } - - $query = qq|UPDATE customer SET | . - qq|customernumber = ?, | . - qq|name = ?, | . - qq|greeting = ?, | . - qq|department_1 = ?, | . - qq|department_2 = ?, | . - qq|street = ?, | . - qq|zipcode = ?, | . - qq|city = ?, | . - qq|country = ?, | . - qq|homepage = ?, | . - qq|contact = ?, | . - qq|phone = ?, | . - qq|fax = ?, | . - qq|email = ?, | . - qq|cc = ?, | . - qq|bcc = ?, | . - qq|notes = ?, | . - qq|discount = ?, | . - qq|creditlimit = ?, | . - qq|terms = ?, | . - qq|business_id = ?, | . - qq|taxnumber = ?, | . - qq|sic_code = ?, | . - qq|language = ?, | . - qq|account_number = ?, | . - qq|bank_code = ?, | . - qq|bank = ?, | . - qq|obsolete = ?, | . - qq|ustid = ?, | . - qq|username = ?, | . - qq|salesman_id = ?, | . - qq|language_id = ?, | . - qq|payment_id = ?, | . - qq|taxzone_id = ?, | . - qq|user_password = ?, | . - qq|c_vendor_id = ?, | . - qq|klass = ? | . - qq|WHERE id = ?|; - my @values = ( - $form->{customernumber}, - $form->{name}, - $form->{greeting}, - $form->{department_1}, - $form->{department_2}, - $form->{street}, - $form->{zipcode}, - $form->{city}, - $form->{country}, - $form->{homepage}, - $form->{contact}, - $form->{phone}, - $form->{fax}, - $form->{email}, - $form->{cc}, - $form->{bcc}, - $form->{notes}, - $form->{discount}, - $form->{creditlimit}, - conv_i($form->{terms}), - conv_i($form->{business}), - $form->{taxnumber}, - $form->{sic}, - $form->{language}, - $form->{account_number}, - $form->{bank_code}, - $form->{bank}, - $form->{obsolete} ? 't' : 'f', - $form->{ustid}, - $form->{username}, - conv_i($form->{salesman_id}), - conv_i($form->{language_id}), - conv_i($form->{payment_id}), - conv_i($form->{taxzone_id}, 0), - $form->{user_password}, - $form->{c_vendor_id}, - conv_i($form->{klass}), - $form->{id} - ); - do_query( $form, $dbh, $query, @values ); - - $query = undef; - if ( $form->{cp_id} ) { - $query = qq|UPDATE contacts SET | . - qq|cp_greeting = ?, | . - qq|cp_title = ?, | . - qq|cp_givenname = ?, | . - qq|cp_name = ?, | . - qq|cp_email = ?, | . - qq|cp_phone1 = ?, | . - qq|cp_phone2 = ?, | . - qq|cp_abteilung = ?, | . - qq|cp_fax = ?, | . - qq|cp_mobile1 = ?, | . - qq|cp_mobile2 = ?, | . - qq|cp_satphone = ?, | . - qq|cp_satfax = ?, | . - qq|cp_project = ?, | . - qq|cp_privatphone = ?, | . - qq|cp_privatemail = ?, | . - qq|cp_birthday = ? | . - qq|WHERE cp_id = ?|; - @values = ( - $form->{cp_greeting}, - $form->{cp_title}, - $form->{cp_givenname}, - $form->{cp_name}, - $form->{cp_email}, - $form->{cp_phone1}, - $form->{cp_phone2}, - $form->{cp_abteilung}, - $form->{cp_fax}, - $form->{cp_mobile1}, - $form->{cp_mobile2}, - $form->{cp_satphone}, - $form->{cp_satfax}, - $form->{cp_project}, - $form->{cp_privatphone}, - $form->{cp_privatemail}, - $form->{cp_birthday}, - $form->{cp_id} - ); - } elsif ( $form->{cp_name} || $form->{cp_givenname} ) { - $query = - qq|INSERT INTO contacts ( cp_cv_id, cp_greeting, cp_title, cp_givenname, | . - qq| cp_name, cp_email, cp_phone1, cp_phone2, cp_abteilung, cp_fax, cp_mobile1, | . - qq| cp_mobile2, cp_satphone, cp_satfax, cp_project, cp_privatphone, cp_privatemail, | . - qq| cp_birthday) | . - qq|VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; - @values = ( - $form->{id}, - $form->{cp_greeting}, - $form->{cp_title}, - $form->{cp_givenname}, - $form->{cp_name}, - $form->{cp_email}, - $form->{cp_phone1}, - $form->{cp_phone2}, - $form->{cp_abteilung}, - $form->{cp_fax}, - $form->{cp_mobile1}, - $form->{cp_mobile2}, - $form->{cp_satphone}, - $form->{cp_satfax}, - $form->{cp_project}, - $form->{cp_privatphone}, - $form->{cp_privatemail}, - $form->{cp_birthday} - ); + # 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"; } - do_query( $form, $dbh, $query, @values ) if ($query); - - # add shipto - $form->add_shipto( $dbh, $form->{id}, "CT" ); - - $rc = $dbh->commit(); - $dbh->disconnect(); - - $main::lxdebug->leave_sub(); - return $rc; -} - -sub save_vendor { - $main::lxdebug->enter_sub(); - - my ( $self, $myconfig, $form ) = @_; - - $form->{taxzone_id} *= 1; - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); - - map( { - $form->{"cp_${_}"} = $form->{"selected_cp_${_}"} - if ( $form->{"selected_cp_${_}"} ); - } qw(title greeting abteilung) ); - $form->{"greeting"} = $form->{"selected_company_greeting"} - if ( $form->{"selected_company_greeting"} ); - - $form->{discount} = $form->parse_amount( $myconfig, $form->{discount} ); - $form->{discount} /= 100; - $form->{creditlimit} = $form->parse_amount( $myconfig, $form->{creditlimit} ); - - my $query; - - if ( $form->{id} ) { - $query = qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'CT')|; - do_query($form, $dbh, $query, $form->{id}); + my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC'; + if ($sortorder !~ /(business|creditlimit|id|discount|itime)/ && !$join_records) { + $sortorder = "lower($sortorder) ${sortdir}"; } else { - $query = qq|SELECT nextval('id')|; - ($form->{id}) = selectrow_query($form, $dbh, $query); + $sortorder .= " ${sortdir}"; + } - $query = qq|INSERT INTO vendor (id, name) VALUES (?, '')|; - do_query($form, $dbh, $query, $form->{id}); + if ($form->{"${cv}number"}) { + $where .= " AND ct.${cv}number ILIKE ?"; + push(@values, like($form->{"${cv}number"})); + } - if ( !$form->{vendornumber} ) { - $form->{vendornumber} = $form->update_defaults( $myconfig, "vendornumber", $dbh ); + foreach my $key (qw(name contact email)) { + if ($form->{$key}) { + $where .= " AND ct.$key ILIKE ?"; + push(@values, like($form->{$key})); } } - $query = - qq|UPDATE vendor SET | . - qq| vendornumber = ?, | . - qq| name = ?, | . - qq| greeting = ?, | . - qq| department_1 = ?, | . - qq| department_2 = ?, | . - qq| street = ?, | . - qq| zipcode = ?, | . - qq| city = ?, | . - qq| country = ?, | . - qq| homepage = ?, | . - qq| contact = ?, | . - qq| phone = ?, | . - qq| fax = ?, | . - qq| email = ?, | . - qq| cc = ?, | . - qq| bcc = ?, | . - qq| notes = ?, | . - qq| terms = ?, | . - qq| discount = ?, | . - qq| creditlimit = ?, | . - qq| business_id = ?, | . - qq| taxnumber = ?, | . - qq| sic_code = ?, | . - qq| language = ?, | . - qq| account_number = ?, | . - qq| bank_code = ?, | . - qq| bank = ?, | . - qq| obsolete = ?, | . - qq| ustid = ?, | . - qq| payment_id = ?, | . - qq| taxzone_id = ?, | . - qq| language_id = ?, | . - qq| username = ?, | . - qq| user_password = ?, | . - qq| v_customer_id = ? | . - qq|WHERE id = ?|; - @values = ( - $form->{vendornumber}, - $form->{name}, - $form->{greeting}, - $form->{department_1}, - $form->{department_2}, - $form->{street}, - $form->{zipcode}, - $form->{city}, - $form->{country}, - $form->{homepage}, - $form->{contact}, - $form->{phone}, - $form->{fax}, - $form->{email}, - $form->{cc}, - $form->{bcc}, - $form->{notes}, - conv_i($form->{terms}), - $form->{discount}, - $form->{creditlimit}, - conv_i($form->{business}), - $form->{taxnumber}, - $form->{sic}, - $form->{language}, - $form->{account_number}, - $form->{bank_code}, - $form->{bank}, - $form->{obsolete} ? 't' : 'f', - $form->{ustid}, - conv_i($form->{payment_id}), - conv_i($form->{taxzone_id}, 0), - conv_i( $form->{language_id}), - $form->{username}, - $form->{user_password}, - $form->{v_customer_id}, - $form->{id} - ); - do_query($form, $dbh, $query, @values); - - $query = undef; - if ( $form->{cp_id} ) { - $query = qq|UPDATE contacts SET | . - qq|cp_greeting = ?, | . - qq|cp_title = ?, | . - qq|cp_givenname = ?, | . - qq|cp_name = ?, | . - qq|cp_email = ?, | . - qq|cp_phone1 = ?, | . - qq|cp_phone2 = ?, | . - qq|cp_abteilung = ?, | . - qq|cp_fax = ?, | . - qq|cp_mobile1 = ?, | . - qq|cp_mobile2 = ?, | . - qq|cp_satphone = ?, | . - qq|cp_satfax = ?, | . - qq|cp_project = ?, | . - qq|cp_privatphone = ?, | . - qq|cp_privatemail = ?, | . - qq|cp_birthday = ? | . - qq|WHERE cp_id = ?|; - @values = ( - $form->{cp_greeting}, - $form->{cp_title}, - $form->{cp_givenname}, - $form->{cp_name}, - $form->{cp_email}, - $form->{cp_phone1}, - $form->{cp_phone2}, - $form->{cp_abteilung}, - $form->{cp_fax}, - $form->{cp_mobile1}, - $form->{cp_mobile2}, - $form->{cp_satphone}, - $form->{cp_satfax}, - $form->{cp_project}, - $form->{cp_privatphone}, - $form->{cp_privatemail}, - $form->{cp_birthday}, - $form->{cp_id} - ); - } elsif ( $form->{cp_name} || $form->{cp_givenname} ) { - $query = - qq|INSERT INTO contacts ( cp_cv_id, cp_greeting, cp_title, cp_givenname, | . - qq| cp_name, cp_email, cp_phone1, cp_phone2, cp_abteilung, cp_fax, cp_mobile1, | . - qq| cp_mobile2, cp_satphone, cp_satfax, cp_project, cp_privatphone, cp_privatemail, | . - qq| cp_birthday) | . - qq|VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; - @values = ( - $form->{id}, - $form->{cp_greeting}, - $form->{cp_title}, - $form->{cp_givenname}, - $form->{cp_name}, - $form->{cp_email}, - $form->{cp_phone1}, - $form->{cp_phone2}, - $form->{cp_abteilung}, - $form->{cp_fax}, - $form->{cp_mobile1}, - $form->{cp_mobile2}, - $form->{cp_satphone}, - $form->{cp_satfax}, - $form->{cp_project}, - $form->{cp_privatphone}, - $form->{cp_privatemail}, - $form->{cp_birthday} - ); + 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}); } - do_query($form, $dbh, $query, @values) if ($query); - - # add shipto - $form->add_shipto( $dbh, $form->{id}, "CT" ); - - $rc = $dbh->commit(); - $dbh->disconnect(); - - $main::lxdebug->leave_sub(); - return $rc; -} -sub delete { - $main::lxdebug->enter_sub(); - - my ( $self, $myconfig, $form ) = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); - - # delete vendor - my $cv = $form->{db} eq "customer" ? "customer" : "vendor"; - my $query = qq|DELETE FROM $cv WHERE id = ?|; - do_query($form, $dbh, $query, $form->{id}); - - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub search { - $main::lxdebug->enter_sub(); - - my ( $self, $myconfig, $form ) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - my $cv = $form->{db} eq "customer" ? "customer" : "vendor"; + 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; + } - my $where = "1 = 1"; - my @values; + 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; + } - my %allowed_sort_columns = - map({ $_, 1 } qw(id customernumber name address contact phone fax email - taxnumber sic_code business invnumber ordnumber quonumber)); - $sortorder = - $form->{sort} && $allowed_sort_columns{$form->{sort}} ? - $form->{sort} : "name"; - $form->{sort} = $sortorder; - $sortorder = "country,city,street" if ($sortorder eq "address"); + 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; + } - if ($form->{"${cv}number"}) { - $where .= " AND ct.${cv}number ILIKE ?"; - push(@values, '%' . $form->{"${cv}number"} . '%'); + 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; } - foreach my $key (qw(name contact email)) { - if ($form->{$key}) { - $where .= " AND ct.$key ILIKE ?"; - push(@values, '%' . $form->{$key} . '%'); - } + 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; } if ( $form->{status} eq 'orphaned' ) { @@ -656,74 +206,160 @@ sub search { } if ($form->{obsolete} eq "Y") { - $where .= qq| AND obsolete|; + $where .= qq| AND ct.obsolete|; } elsif ($form->{obsolete} eq "N") { - $where .= qq| AND NOT obsolete|; + $where .= qq| AND NOT ct.obsolete|; } if ($form->{business_id}) { - $where .= qq| AND (business_id = ?)|; + $where .= qq| AND (ct.business_id = ?)|; push(@values, conv_i($form->{business_id})); } + if ($form->{salesman_id}) { + $where .= qq| AND (ct.salesman_id = ?)|; + push(@values, conv_i($form->{salesman_id})); + } + + if($form->{insertdatefrom}) { + $where .= qq| AND (ct.itime::DATE >= ?)|; + push@values, conv_date($form->{insertdatefrom}); + } + + if($form->{insertdateto}) { + $where .= qq| AND (ct.itime::DATE <= ?)|; + push @values, conv_date($form->{insertdateto}); + } + + 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; + } + + if (($form->{create_zugferd_invoices} // '') ne '') { + $where .= qq| AND (ct.create_zugferd_invoices = ?)|; + push @values, $form->{create_zugferd_invoices}; + } + + if ($form->{all_phonenumbers}) { + my $search_term = trim($form->{all_phonenumbers}); + $search_term =~ s{\p{WSpace}+}{}g; + $search_term = join ' *', split(//, $search_term); + + $where .= qq| AND (ct.phone ~* ? OR + ct.fax ~* ? OR + ct.id IN + (SELECT cp_cv_id FROM contacts + WHERE cp_phone1 ~* ? OR + cp_phone2 ~* ? OR + cp_fax ~* ? OR + cp_mobile1 ~* ? OR + cp_mobile2 ~* ? OR + cp_satphone ~* ? OR + cp_satfax ~* ? OR + cp_privatphone ~* ? + ) + )|; + push @values, ($search_term)x10; + } + + my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT', + 'trans_id_field' => 'ct.id', + 'filter' => $form); + + if ($cvar_where) { + $where .= qq| AND ($cvar_where)|; + push @values, @cvar_values; + } + + 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) | : ''; + + 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.*, b.description AS business | . + 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 ($form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber}) { - my ($ar, $union, $module); - $query = ""; + if ($join_records) { + my $union = "UNION"; if ($form->{l_invnumber}) { my $ar = $cv eq 'customer' ? 'ar' : 'ap'; my $module = $ar eq 'ar' ? 'is' : 'ir'; - - $query = - qq|SELECT ct.*, b.description AS business, | . - qq| a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | . + 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')|; - - $union = qq|UNION|; } if ( $form->{l_ordnumber} ) { - if ($union eq "UNION") { - push(@values, @saved_values); - } + push(@values, @saved_values); $query .= - qq| $union | . - qq|SELECT ct.*, b.description AS business,| . - qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | . + 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')|; - - $union = qq|UNION|; } if ( $form->{l_quonumber} ) { - if ($union eq "UNION") { - push(@values, @saved_values); - } + push(@values, @saved_values); $query .= - qq| $union | . - qq|SELECT ct.*, b.description AS business, | . - qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | . + 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')|; } } @@ -731,8 +367,6 @@ sub search { $query .= qq| ORDER BY $sortorder|; $form->{CT} = selectall_hashref_query($form, $dbh, $query, @values); - map({ my $ref = $_; $ref->{address} = join(" ", map({ $ref->{$_} } qw(street zipcode city country))); } - @{ $form->{CT} }); $main::lxdebug->leave_sub(); } @@ -741,86 +375,149 @@ sub get_contact { $main::lxdebug->enter_sub(); my ( $self, $myconfig, $form ) = @_; - my $dbh = $form->dbconnect($myconfig); + + 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); + my $ref = $sth->fetchrow_hashref("NAME_lc"); map { $form->{$_} = $ref->{$_} } keys %$ref; + $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); + $sth->finish; - $dbh->disconnect; $main::lxdebug->leave_sub(); } -sub get_shipto { +sub get_bank_info { $main::lxdebug->enter_sub(); - my ( $self, $myconfig, $form ) = @_; - my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT * FROM shipto WHERE shipto_id = ?|; - my $sth = prepare_execute_query($form, $dbh, $query, $form->{shipto_id}); + my $self = shift; + my %params = @_; - my $ref = $sth->fetchrow_hashref(NAME_lc); + Common::check_params(\%params, qw(vc id)); - map { $form->{$_} = $ref->{$_} } keys %$ref; + my $myconfig = \%main::myconfig; + my $form = $main::form; - $sth->finish; - $dbh->disconnect; + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + 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})|; + + 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 get_delivery { - $main::lxdebug->enter_sub(); +sub search_contacts { + $::lxdebug->enter_sub; - my ( $self, $myconfig, $form ) = @_; - my $dbh = $form->dbconnect($myconfig); + my $self = shift; + my %params = @_; + + my $dbh = $params{dbh} || $::form->get_standard_dbh; + + my %sortspecs = ( + 'cp_name' => 'cp_name, cp_givenname', + 'vcname' => 'vcname, cp_name, cp_givenname', + 'vcnumber' => 'vcnumber, cp_name, cp_givenname', + ); + + 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); + + my $order_by = $sortcols{$::form->{sort}} ? $::form->{sort} : 'cp_name'; + $::form->{sort} = $order_by; + $order_by = $sortspecs{$order_by} if ($sortspecs{$order_by}); - my $arap = $form->{db} eq "vendor" ? "ap" : "ar"; - my $db = $form->{db} eq "customer" ? "customer" : "vendor"; + my $sortdir = $::form->{sortdir} ? 'ASC' : 'DESC'; + $order_by =~ s/,/ ${sortdir},/g; + $order_by .= " $sortdir"; - my $where = " WHERE 1=1 "; + my @where_tokens = (); my @values; - if ($form->{shipto_id} && ($arap eq "ar")) { - $where .= "AND ${arap}.shipto_id = ?"; - push(@values, $form->{shipto_id}); - } else { - $where .= "AND ${arap}.${db}_id = ?"; - push(@values, $form->{id}); + 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 (($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); + } + + push @where_tokens, map { "($_)" } join ' OR ', @tokens; } - if ($form->{from}) { - $where .= "AND ${arap}.transdate >= ?"; - push(@values, conv_date($form->{from})); + 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; } - if ($form->{to}) { - $where .= "AND ${arap}.transdate <= ?"; - push(@values, conv_date($form->{to})); + + 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'); + } + + 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'; } - my $query = - qq|SELECT s.shiptoname, i.qty, | . - qq| ${arap}.transdate, ${arap}.invnumber, ${arap}.ordnumber, | . - qq| i.description, i.unit, i.sellprice | . - qq|FROM $arap | . - qq|LEFT JOIN shipto s ON | . - ($arap eq "ar" - ? qq|(ar.shipto_id = s.shipto_id) | - : qq|(ap.id = s.trans_id) |) . - qq|LEFT JOIN invoice i ON (${arap}.id = i.trans_id) | . - qq|LEFT join parts p ON (p.id = i.parts_id) | . - $where . - qq|ORDER BY ${arap}.transdate DESC LIMIT 15|; - - $form->{DELIVERY} = selectall_hashref_query($form, $dbh, $query, @values); - - $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;