[set page_banner]Customer[/set] [set fm_class]Contact[/set] [set help_name]customer[/set] [set page_title]Customer[/set] [set icon_name]icon_people.gif[/set] @_FM_STD_HEAD_@ [comment] Todo: validation - does entry with same customer exist? [/comment] [query list=1 sql="SELECT custgrp_id FROM co_custgrp WHERE custgrp_nr = '[value custgroup]'"] [seti custgrp_id][sql-param custgrp_id][/seti] [/query] [seti sqlSentence] BEGIN TRANSACTION; [perl] my $trans; my $action = $Values->{"mv_action"}; my $contact_id = $Values->{"select_customer"} || "currval('co_contactsequence')"; my $custgrp_id = $Scratch->{"custgrp_id"} || 'null'; if ($action eq "Update") { $trans = "UPDATE co_customer SET customernr = '[value custnr]', type = [value custtype], custgrp_id = $custgrp_id, credit_code = [value creditterms], deliverycode = [value deliveryterms], paycode = [value paymentterms], freightcode = [value freightcode], updated='now' WHERE contact_id = $contact_id; UPDATE co_address SET address1 = '[value addr1]', address2 = '[value addr2]', address3 = '[value addr3]', postalcode = '[value postalcode]', city = '[value city]', updated='now' WHERE contact_id = $contact_id;"; if ($Values->{"custtype"} == 60001) { $trans .= "UPDATE co_person SET firstname = '[value fname]', lastname='[value lname]', updated='now' WHERE contact_id = $contact_id;"; } else { $trans .= "UPDATE co_company SET name = '[value compname]', attention='[value attention]', updated='now' WHERE contact_id = $contact_id;"; } } elsif ($action eq "Delete") { $trans .= "DELETE FROM co_customer WHERE contact_id = $contact_id; DELETE FROM co_address WHERE contact_id = $contact_id; DELETE FROM co_person WHERE contact_id = $contact_id; DELETE FROM co_company WHERE contact_id = $contact_id; DELETE FROM ar_custacct WHERE contact_id = $contact_id;"; } else { $trans .= "INSERT INTO co_contact (type) VALUES (50001); INSERT INTO co_customer (contact_id,customernr,type,custgrp_id,credit_code,deliverycode,paycode,freightcode) SELECT $contact_id,'[value custnr]',[value custtype],$custgrp_id,[value creditterms],[value deliveryterms],[value paymentterms], [value freightcode]; INSERT INTO co_address (contact_id,type,country_id,address1,address2,address3,postalcode,city) SELECT $contact_id,70001,48,'[value addr1]','[value addr2]','[value addr3]','[value postalcode]','[value city]';"; if ($Values->{"custtype"} == 60001) { $trans .= "INSERT INTO co_person (contact_id,firstname,lastname) SELECT $contact_id, '[value fname]', '[value lname]';"; } else { $trans .= "INSERT INTO co_company (contact_id,name,attention) SELECT $contact_id, '[value compname]', '[value attention]';"; }; $trans .= "INSERT INTO ar_custacct (contact_id,type) VALUES ($contact_id, 100001);"; }; if (!$Values->{"phonenr"} and $Scratch->{"phonenr"}) { $trans .= "DELETE FROM co_telephone WHERE contact_id = $contact_id AND type=90001;"; } elsif ($Scratch->{"phonenr"}) { $trans .= "UPDATE co_telephone SET phonenr = '[value phonenr]', updated='now' WHERE contact_id = $contact_id AND type=90001;"; } elsif ($Values->{"phonenr"}) { $trans .= "INSERT INTO co_telephone (contact_id,type,phonenr,updated) VALUES ($contact_id,90001,'[value phonenr]','now');"; } if (!$Values->{"mobilenr"} and $Scratch->{"mobilenr"}) { $trans .= "DELETE FROM co_telephone WHERE contact_id = $contact_id AND type=90002;"; } elsif ($Scratch->{"mobilenr"}) { $trans .= "UPDATE co_telephone SET phonenr = '[value mobilenr]', updated='now' WHERE contact_id = $contact_id AND type=90002;"; } elsif ($Values->{"mobilenr"}) { $trans .= "INSERT INTO co_telephone (contact_id,type,phonenr,updated) VALUES ($contact_id,90002,'[value mobilenr]','now');"; } if (!$Values->{"faxnr"} and $Scratch->{"faxnr"}) { $trans .= "DELETE FROM co_telephone WHERE contact_id = $contact_id AND type=90003;"; } elsif ($Scratch->{"faxnr"}) { $trans .= "UPDATE co_telephone SET phonenr = '[value faxnr]', updated='now' WHERE contact_id = $contact_id AND type=90003;"; } elsif ($Values->{"faxnr"}) { $trans .= "INSERT INTO co_telephone (contact_id,type,phonenr,updated) VALUES ($contact_id,90003,'[value faxnr]','now');"; } if (!$Values->{"emailaddr"} and $Scratch->{"emailaddr"}) { $trans .= "DELETE FROM co_url WHERE contact_id = $contact_id AND type=80001;"; } elsif ($Scratch->{"emailaddr"}) { $trans .= "UPDATE co_url SET url = '[value emailaddr]', updated='now' WHERE contact_id = $contact_id AND type=80001;"; } elsif ($Values->{"emailaddr"}) { $trans .= "INSERT INTO co_url (contact_id,type,url,updated) VALUES ($contact_id,80001,'[value emailaddr]','now');"; } if (!$Values->{"webaddr"} and $Scratch->{"webaddr"}) { $trans .= "DELETE FROM co_url WHERE contact_id = $contact_id AND type=80002;"; } elsif ($Scratch->{"webaddr"}) { $trans .= "UPDATE co_url SET url = '[value webaddr]', updated='now' WHERE contact_id = $contact_id AND type=80002;"; } elsif ($Values->{"webaddr"}) { $trans .= "INSERT INTO co_url (contact_id,type,url,updated) VALUES ($contact_id,80002,'[value webaddr]','now');"; } $trans; [/perl] COMMIT; [/seti] [if value mv_action eq Update] [set returnpage]freemoney/customerList[/set] [set leadtext]The customer entry has been updated.[/set] [elsif value mv_action eq Delete] [set returnpage]freemoney/customerList[/set] [set leadtext]The customer entry has been deleted.[/set] [/elsif] [else] [set returnpage]freemoney/customer[/set] [set leadtext]The customer entry has been added.[/set] [/else] [/if] [value name=mv_action set=""] [try][sql interpolate=1][scratch sqlSentence][/sql][/try]
[L][scratch leadtext][/L]

[catch] [L]There was an error adding the new customer entry. Please check your data.[/L]

[/catch] [L]BACK[/L] @_FM_STD_FOOTER_@