1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110
|
CREATE OR REPLACE FUNCTION alim_client() RETURNS void AS
$func$
DECLARE
r "Client_transfert"%ROWTYPE;
c INT := 1;
BEGIN
FOR r IN SELECT "Code Client", "Nom Client Fact", "Contact", "Adr Fact", "CP Fact",
"Ville Fact", "Pays Fact", "Fr - CE - Export Fact", "Tel1 Fact",
"Tel2 Fact", "Fax Fact", "E-Mail Fact", "Adresse Internet Fact",
"Nom Client Liv", "Contact Liv", "Adr Liv", "CP Liv", "Ville Liv",
"Pays Liv", "FR - CE - Export Liv", "Tel1 Liv", "Tel2 Liv", "Fax Liv",
"E-Mail Liv", "Adresse Internet Liv", "Remise Client", "Modedepaiement",
"Délais de paiement", "Représentant", "Type_client", "Code Banque",
"Code Guichet", "Num Compte", "Clé RIB", "Nom Banque", "IBAN1",
"IBAN2", "BIC", "Titulaire du Compte", "SIREN", "TVA Intracommunautaire",
"Liste Rouge", "Liste Noire", "Commentaire", "TotalPointFidelite" FROM "Client_transfert" LOOP
IF "r.Contact" =! NULL THEN
INSERT INTO t_contact (id_client, nom_contact) VALUES (c, "Contact");
END IF;
IF "r.Contact Fact" =! NULL THEN
INSERT INTO t_contact (id_client, nom_contact) VALUES (c, "Contact Fact");
END IF;
IF "r.Type_client" = "Club" THEN
INSERT INTO t_client (id_representant) VALUES ('2');
ELSE IF "r.Type_client" = "MAGASIN" THEN
INSERT INTO t_client (id_representant) VALUES ('1');
ELSE IF "r.Type_client" = "SPONSOR" THEN
INSERT INTO t_client (id_representant) VALUES ('3');
END IF;
IF "r.Délais de paiement" = "30 j Fin de Mois" THEN
INSERT INTO t_client (id_delais_paiement) VALUES ('1');
ELSE IF "r.Délais de paiement" = "30 j Fin de Mois le 10" THEN
INSERT INTO t_client (id_delais_paiement) VALUES ('2');
ELSE IF "r.Délais de paiement" = "30 j net" THEN
INSERT INTO t_client (id_delais_paiement) VALUES ('3');
ELSE IF "r.Délais de paiement" = "60 j Fin de Mois" THEN
INSERT INTO t_client (id_delais_paiement) VALUES ('4');
ELSE IF "r.Délais de paiement" = "60 j net" THEN
INSERT INTO t_client (id_delais_paiement) VALUES ('5');
ELSE IF "r.Délais de paiement" = "90 j Fin de Mois le 10" THEN
INSERT INTO t_client (id_delais_paiement) VALUES ('6');
ELSE IF "r.Délais de paiement" = "Comptant" THEN
INSERT INTO t_client (id_delais_paiement) VALUES ('7');
ELSE IF "r.Délais de paiement" = "Contre-rembours." THEN
INSERT INTO t_client (id_delais_paiement) VALUES ('8');
ELSE IF "r.Délais de paiement" = "D avance" THEN
INSERT INTO t_client (id_delais_paiement) VALUES ('9');
END IF;
IF "r.modedepaiement" = "Chèque" THEN
INSERT INTO t_client (id_mode_paiement) VALUES ('1');
ELSE IF "r.modedepaiement" = "Contre-rembours." OR "Contre-remboursement" THEN
INSERT INTO t_client (id_mode_paiement) VALUES ('2');
ELSE IF "r.modedepaiement" = "Virement" THEN
INSERT INTO t_client (id_mode_paiement) VALUES ('3');
ELSE IF "r.modedepaiement" = "Espèce" THEN
INSERT INTO t_client (id_mode_paiement) VALUES ('4');
ELSE IF "r.modedepaiement" = "Effet accepté" THEN
INSERT INTO t_client (id_mode_paiement) VALUES ('5');
ELSE IF "r.modedepaiement" = "Effet non accepté" THEN
INSERT INTO t_client (id_mode_paiement) VALUES ('6');
END IF;
IF "r.Représentant" = "xxxxxx" THEN
INSERT INTO t_client (id_representant) VALUES ('2');
ELSE IF "r.Représentant" = "xxxxxx" THEN
INSERT INTO t_client (id_representant) VALUES ('3');
ELSE IF "r.Représentant" = "xxxxxxx" THEN
INSERT INTO t_client (id_representant) VALUES ('4');
ELSE IF "r.Représentant" = "xxxxxxx" THEN
INSERT INTO t_client (id_representant) VALUES ('5');
ELSE IF "r.Représentant" = "xxxxxxx" THEN
INSERT INTO t_client (id_representant) VALUES ('6');
ELSE IF "r.Représentant" = "xxxxxx" THEN
INSERT INTO t_client (id_representant) VALUES ('7');
ELSE IF "r.Représentant" = "xxxxx" THEN
INSERT INTO t_client (id_representant) VALUES ('8');
ELSE IF "r.Représentant" = "xxxxxx" THEN
INSERT INTO t_client (id_representant) VALUES ('15');
ELSE IF "r.Représentant" = "xxxxxxxx" THEN
INSERT INTO t_client (id_representant) VALUES ('10');
ELSE IF "r.Représentant" = "xxxxxxx" THEN
INSERT INTO t_client (id_representant) VALUES ('12');
ELSE IF "r.Représentant" = "xxxxxxx" THEN
INSERT INTO t_client (id_representant) VALUES ('13');
END IF;
INSERT INTO t_client (
nom_f_client, adresse_f_client, cp_f_client, ville_f_client, pays_f_client,
fixe_f_client, mobile_f_client, fax_f_client, email_f_client, site_web_f_client,
nom_l_client, adresse_l_client, cp_l_client, ville_l_client, pays_l_client,
fixe_l_client, mobile_l_client, fax_l_client, email_l_client, site_web_l_client,
liste_rouge_client, pts_fidel_client, siren_client, num_intracomm_client,
chemin_rep_client, remise_client)
VALUES (
"r.Nom Client Fact", "r.Adr Fact", "r.CP Fact", "r.Ville Fact", "r.Pays Fact",
"r.Tel1 Fact", "r.Tel2 Fact", "r.Fax Fact", "r.E-Mail Fact", "r.Adresse Internet Fact",
"r.Nom Client Liv", "r.Adr Liv", "r.CP Liv", "r.Ville Liv", "r.Pays Liv", "r.Tel1 Liv", "r.Tel2 Liv", "r.Fax Liv",
"r.E-Mail Liv", "r.Adresse Internet Liv", "r.Liste Rouge", "r.Remise Client", "r.TotalPointFidelite", "r.SIREN", "r.TVA Intracommunautaire", "r.Remise Client");
c := c + 1;
END LOOP;
END;
$func$
LANGUAGE 'plpgsql'; |
Partager