X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCT.pm;h=0468c379d210001590c5c6f5a0094e65ea5f6605;hb=5df2b57a097f66f6cecba0aa577c7aaba3422ab6;hp=db715ba0126fda3a36aa557ea75a082f16af955c;hpb=52c9a08d4fc28b6027b8f6044e85043ec9e75335;p=kivitendo-erp.git diff --git a/SL/CT.pm b/SL/CT.pm index db715ba01..0468c379d 100644 --- a/SL/CT.pm +++ b/SL/CT.pm @@ -36,8 +36,14 @@ #====================================================================== package CT; + use Data::Dumper; + +use SL::Common; +use SL::CVar; use SL::DBUtils; +use SL::FU; +use SL::Notes; sub get_tuple { $main::lxdebug->enter_sub(); @@ -70,6 +76,52 @@ sub get_tuple { selectrow_query($form, $dbh, $query, $form->{salesman_id}); } + my ($employee_id) = selectrow_query($form, $dbh, qq|SELECT id FROM employee WHERE login = ?|, $form->{login}); + $query = + qq|SELECT n.*, n.itime::DATE AS created_on, + e.name AS created_by_name, e.login AS created_by_login + FROM notes n + LEFT JOIN employee e ON (n.created_by = e.id) + WHERE (n.trans_id = ?) AND (n.trans_module = 'ct')|; + $form->{NOTES} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id})); + + $query = + qq|SELECT fu.follow_up_date, fu.done AS follow_up_done, e.name AS created_for_name, e.name AS created_for_login + FROM follow_ups fu + LEFT JOIN employee e ON (fu.created_for_user = e.id) + WHERE (fu.note_id = ?) + AND NOT COALESCE(fu.done, FALSE) + AND ( (fu.created_by = ?) + OR (fu.created_by IN (SELECT DISTINCT what FROM follow_up_access WHERE who = ?)))|; + $sth = prepare_query($form, $dbh, $query); + + foreach my $note (@{ $form->{NOTES} }) { + do_statement($form, $sth, $query, conv_i($note->{id}), conv_i($note->{created_by}), conv_i($employee_id)); + $ref = $sth->fetchrow_hashref(); + + map { $note->{$_} = $ref->{$_} } keys %{ $ref } if ($ref); + } + + $sth->finish(); + + if ($form->{edit_note_id}) { + $query = + qq|SELECT n.id AS NOTE_id, n.subject AS NOTE_subject, n.body AS NOTE_body, + fu.id AS FU_id, fu.follow_up_date AS FU_date, fu.done AS FU_done, fu.created_for_user AS FU_created_for_user + FROM notes n + LEFT JOIN follow_ups fu ON ((n.id = fu.note_id) AND NOT COALESCE(fu.done, FALSE)) + WHERE n.id = ?|; + $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{edit_note_id})); + + if ($ref) { + foreach my $key (keys %{ $ref }) { + my $new_key = $key; + $new_key =~ s/^([^_]+)/\U\1\E/; + $form->{$new_key} = $ref->{$key}; + } + } + } + # check if it is orphaned my $arap = ( $form->{db} eq 'customer' ) ? "ar" : "ap"; $query = @@ -108,7 +160,7 @@ sub populate_drop_down_boxes { $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 = ?|; + $query = qq|SELECT cp_id, cp_name, cp_givenname FROM contacts WHERE cp_cv_id = ? ORDER BY cp_name|; $form->{CONTACTS} = selectall_hashref_query($form, $dbh, $query, $form->{id}); # get languages @@ -381,6 +433,14 @@ sub save_customer { # add shipto $form->add_shipto( $dbh, $form->{id}, "CT" ); + $self->_save_note('dbh' => $dbh); + $self->_delete_selected_notes('dbh' => $dbh); + + CVar->save_custom_variables('dbh' => $dbh, + 'module' => 'CT', + 'trans_id' => $form->{id}, + 'variables' => $form); + $rc = $dbh->commit(); $dbh->disconnect(); @@ -410,11 +470,7 @@ sub save_vendor { my $query; - if ( $form->{id} ) { - $query = qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'CT')|; - do_query($form, $dbh, $query, $form->{id}); - - } else { + if (!$form->{id}) { $query = qq|SELECT nextval('id')|; ($form->{id}) = selectrow_query($form, $dbh, $query); @@ -499,22 +555,31 @@ sub save_vendor { conv_i( $form->{language_id}), $form->{username}, $form->{user_password}, - conv_i($form->{v_customer_id}), + $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 = ? | . + $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}, @@ -524,26 +589,59 @@ sub save_vendor { $form->{cp_email}, $form->{cp_phone1}, $form->{cp_phone2}, - $form->{cp_id}); + $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, cp_name, cp_email, cp_phone1, cp_phone2) | . - qq|VALUES (?, ?, ?, ?, ?, ?, ?, ?)|; + 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 = ( - conv_i($form->{id}), + $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_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} + ); } do_query($form, $dbh, $query, @values) if ($query); # add shipto $form->add_shipto( $dbh, $form->{id}, "CT" ); + $self->_save_note('dbh' => $dbh); + $self->_delete_selected_notes('dbh' => $dbh); + + CVar->save_custom_variables('dbh' => $dbh, + 'module' => 'CT', + 'trans_id' => $form->{id}, + 'variables' => $form); + $rc = $dbh->commit(); $dbh->disconnect(); @@ -582,13 +680,18 @@ sub search { my @values; my %allowed_sort_columns = - map({ $_, 1 } qw(id customernumber name address contact phone fax email + map({ $_, 1 } qw(id customernumber vendornumber 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 ($sortorder eq "address") { + $sortorder = "lower(country), lower(city), lower(street)"; + } elsif ($sortorder ne 'id') { + $sortorder = "lower($sortorder)"; + } if ($form->{"${cv}number"}) { $where .= " AND ct.${cv}number ILIKE ?"; @@ -632,6 +735,15 @@ sub search { push(@values, conv_i($form->{business_id})); } + 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 $query = qq|SELECT ct.*, b.description AS business | . qq|FROM $cv ct | . @@ -716,6 +828,14 @@ sub get_contact { 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; @@ -734,6 +854,13 @@ sub get_shipto { map { $form->{$_} = $ref->{$_} } keys %$ref; + $query = qq|SELECT COUNT(shipto_id) AS used FROM ( + SELECT shipto_id FROM oe UNION + SELECT shipto_id FROM ar UNION + SELECT shipto_id FROM delivery_orders + ) AS stid WHERE shipto_id = ? OR ? = 0|; + ($form->{shiptoused}) = selectfirst_array_query($form, $dbh, $query, ($form->{shipto_id})x2); + $sth->finish; $dbh->disconnect; @@ -789,4 +916,117 @@ sub get_delivery { $main::lxdebug->leave_sub(); } +sub _save_note { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + my $form = $main::form; + + Common::check_params(\%params, 'dbh'); + + if (!$form->{NOTE_subject}) { + $main::lxdebug->leave_sub(); + return; + } + + my $dbh = $params{dbh}; + + my %follow_up; + my %note = ( + 'id' => $form->{NOTE_id}, + 'subject' => $form->{NOTE_subject}, + 'body' => $form->{NOTE_body}, + 'trans_id' => $form->{id}, + 'trans_module' => 'ct', + ); + + $note{id} = Notes->save(%note); + + if ($form->{FU_date}) { + %follow_up = ( + 'id' => $form->{FU_id}, + 'note_id' => $note{id}, + 'follow_up_date' => $form->{FU_date}, + 'created_for_user' => $form->{FU_created_for_user}, + 'done' => $form->{FU_done} ? 1 : 0, + 'subject' => $form->{NOTE_subject}, + 'body' => $form->{NOTE_body}, + 'LINKS' => [ + { + 'trans_id' => $form->{id}, + 'trans_type' => $form->{db} eq 'customer' ? 'customer' : 'vendor', + 'trans_info' => $form->{name}, + }, + ], + ); + + $follow_up{id} = FU->save(%follow_up); + + } elsif ($form->{FU_id}) { + do_query($form, $dbh, qq|DELETE FROM follow_up_links WHERE follow_up_id = ?|, conv_i($form->{FU_id})); + do_query($form, $dbh, qq|DELETE FROM follow_ups WHERE id = ?|, conv_i($form->{FU_id})); + } + + delete @{$form}{grep { /^NOTE_|^FU_/ } keys %{ $form }}; + + $main::lxdebug->leave_sub(); +} + +sub _delete_selected_notes { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + Common::check_params(\%params, 'dbh'); + + my $form = $main::form; + my $dbh = $params{dbh}; + + foreach my $i (1 .. $form->{NOTES_rowcount}) { + next unless ($form->{"NOTE_delete_$i"} && $form->{"NOTE_id_$i"}); + + Notes->delete('dbh' => $params{dbh}, + 'id' => $form->{"NOTE_id_$i"}); + } + + $main::lxdebug->leave_sub(); +} + +sub delete_shipto { + $main::lxdebug->enter_sub(); + + my $self = shift; + my $shipto_id = shift; + + my $form = $main::form; + my %myconfig = %main::myconfig; + my $dbh = $form->get_standard_dbh(\%myconfig); + + do_query($form, $dbh, qq|UPDATE shipto SET trans_id = NULL WHERE shipto_id = ?|, $shipto_id); + + $dbh->commit(); + + $main::lxdebug->leave_sub(); +} + +sub delete_shipto { + $main::lxdebug->enter_sub(); + + my $self = shift; + my $shipto_id = shift; + + my $form = $main::form; + my %myconfig = %main::myconfig; + my $dbh = $form->get_standard_dbh(\%myconfig); + + do_query($form, $dbh, qq|UPDATE contacts SET cp_cv_id = NULL WHERE cp_id = ?|, $shipto_id); + + $dbh->commit(); + + $main::lxdebug->leave_sub(); +} + 1;