[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_@