# -----------------------------------------------------------------------------
#
# Codes Postaux et INSEE des communes de France
#
#	Chuis
#
# 14/09/0 10:58 mad@ktaland.com
# 15/09/2000 18:38
#

# -----------------------------------------------------------------------------
#
drop table if exists CDF_COMMUNES ;

create table CDF_COMMUNES (

	COM_ID	int NOT NULL auto_increment ,

	COM_LIBELLE		varchar(60) not null ,
	COM_CODE_POSTAL	char(5) not null ,
	COM_CODE_INSEE	int(6) not null ,

	DEP_CODE	int not null ,

	KEY ( COM_CODE_POSTAL ) ,
	KEY ( DEP_CODE ) ,
	PRIMARY KEY ( COM_ID )
);

load data infile
	#kawa: '/home/www/test.ktaland/codepostaux/communes.txt'
	#zbox: '/home/httpd/html/dev/codepostaux/communes.txt'
	'/home/compt1/www/work/flohic/communes.txt'
	into table CDF_COMMUNES
	ignore 1 lines
	(
		COM_LIBELLE, COM_CODE_POSTAL, COM_CODE_INSEE
	);

update
	CDF_COMMUNES 
set
	DEP_CODE = LEFT( COM_CODE_POSTAL, 2 )
;

# -----------------------------------------------------------------------------
#
drop table if exists CDF_DEPARTEMENTS ;

CREATE TABLE CDF_DEPARTEMENTS (

	DEP_CODE	int not null ,
	DEP_LIBELLE	varchar(60) not null ,
	REG_CODE	int not null,

	UNIQUE ( DEP_LIBELLE ) ,
	PRIMARY KEY ( DEP_CODE )
);

load data infile
	#kawa:'/home/www/test.ktaland/codepostaux/departements.txt'
	#zbox: '/home/httpd/html/dev/codepostaux/departements.txt'
	'/home/compt1/www/work/flohic/departements.txt'
	into table CDF_DEPARTEMENTS
	ignore 1 lines
	(
		DEP_CODE, DEP_LIBELLE, REG_CODE
	);

# -----------------------------------------------------------------------------
#
drop table if exists CDF_REGIONS ;

CREATE TABLE CDF_REGIONS (

	REG_CODE	int not null ,
	REG_LIBELLE	varchar(60) not null ,

	UNIQUE ( REG_LIBELLE ) ,
	PRIMARY KEY ( REG_CODE )
);

#
# datas from afdas : AFD_REGREP
#

INSERT INTO CDF_REGIONS VALUES ('11','ILE-DE-FRANCE');
INSERT INTO CDF_REGIONS VALUES ('21','CHAMPAGNE-ARDENNE');
INSERT INTO CDF_REGIONS VALUES ('22','PICARDIE');
INSERT INTO CDF_REGIONS VALUES ('23','HAUTE-NORMANDIE');
INSERT INTO CDF_REGIONS VALUES ('24','CENTRE');
INSERT INTO CDF_REGIONS VALUES ('25','BASSE-NORMANDIE');
INSERT INTO CDF_REGIONS VALUES ('26','BOURGOGNE');
INSERT INTO CDF_REGIONS VALUES ('31','NORD-PAS-DE-CALAIS');
INSERT INTO CDF_REGIONS VALUES ('41','LORRAINE');
INSERT INTO CDF_REGIONS VALUES ('42','ALSACE');
INSERT INTO CDF_REGIONS VALUES ('43','FRANCHE-COMTE');
INSERT INTO CDF_REGIONS VALUES ('52','PAYS DE LA LOIRE');
INSERT INTO CDF_REGIONS VALUES ('53','BRETAGNE');
INSERT INTO CDF_REGIONS VALUES ('54','POITOU-CHARENTES');
INSERT INTO CDF_REGIONS VALUES ('72','AQUITAINE');
INSERT INTO CDF_REGIONS VALUES ('73','MIDI-PYRENEES');
INSERT INTO CDF_REGIONS VALUES ('74','LIMOUSIN');
INSERT INTO CDF_REGIONS VALUES ('82','RHONE-ALPES');
INSERT INTO CDF_REGIONS VALUES ('83','AUVERGNE');
INSERT INTO CDF_REGIONS VALUES ('91','LANGUEDOC-ROUSSILLON');
INSERT INTO CDF_REGIONS VALUES ('93','PROVENCE-COTE-D\'AZUR');
INSERT INTO CDF_REGIONS VALUES ('94','CORSE');
INSERT INTO CDF_REGIONS VALUES ('99','D.O.M-T.O.M.');

# -----------------------------------------------------------------------------