#!/bin/sh
. ./patch-lib

case $DEBUG in
  1) debug_mode 1
     ;;
  *)
     ;;
esac

log "Patch 2.1 start..."

# prospection
patch211() {
	intit="[PATCH 211] Ajoute prospection"
	echo -e "\t$intit"
	log "$intit"
	exec_sql "BEGIN ; \
insert into profil_societe (nom, code, codecrea, ref_statut) VALUES ('prospect', 'PRO', 'vital_generation.sql', (select uid from statut where nom='valide')); \
\
insert into structtable (ref_structgroupe,nom,denomination,rang) values ((select min(uid) from structgroupe),'view_prospect','view_prospect','6'); \
\
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'uid','identifiant','1',coalesce((select uid from type_edition where nom='int4'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'authmodif','auteur de la dernière modification','1',coalesce((select uid from type_edition where nom='ref'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom='userid'))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'datemodif','date de dernière modification','1',coalesce((select uid from type_edition where nom='timestamp'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'datecrea','date de création','1',coalesce((select uid from type_edition where nom='timestamp'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'authcrea','auteur de la création','1',coalesce((select uid from type_edition where nom='ref'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom='userid'))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'codecrea','code de création','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'ref_statut','ref_statut','1',coalesce((select uid from type_edition where nom='ref'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom='statut'))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'codemodif','code de dernière modification','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'nom','nom','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'email','email','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'web','web','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'cp','cp','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'adresse1','adresse1','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'adresse2','adresse2','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'ville','ville','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'tel','tel','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'tel2','tel2','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'fax','fax','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'scan_rib','scan_rib','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'code','code','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'ue_tva','ue_tva','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'logo','logo','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'siret','siret','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'raison','raison','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'commentaire','commentaire','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'siren','siren','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
insert into structchamp (ref_structtable,nom,denomination,rang,ref_type_edition,table_reference) VALUES ((select uid from structtable where nom='view_prospect'),'pied_page','pied_page','1',coalesce((select uid from type_edition where nom='text'),(select uid from type_edition where nom='text'),0),coalesce((select uid from structtable where nom=''))); \
CREATE VIEW view_prospect AS SELECT s.* FROM societe s, societe_avec_profils sp WHERE sp.ref_societe=s.uid AND sp.ref_profil_societe=(SELECT uid FROM profil_societe WHERE nom='prospect'); \
update structtable set table_mere=(select uid from structtable where nom='societe') where nom='view_prospect'; \
CREATE RULE rule_prospect_insert AS \
ON INSERT TO view_prospect DO INSTEAD ( \
	INSERT INTO societe(uid, authmodif, datemodif, datecrea, nom, authcrea, codecrea, ref_statut, codemodif, email, cp, adresse1, tel2, adresse2, ville, tel, scan_rib, fax, web, code, ue_tva, logo, siret, raison, commentaire) \
	VALUES (new.uid, new.authmodif, new.datemodif, new.datecrea, new.nom, new.authcrea, new.codecrea, new.ref_statut, new.codemodif, new.email, new.cp, new.adresse1, new.tel2, new.adresse2, new.ville, new.tel, new.scan_rib, new.fax, new.web, new.code, new.ue_tva, new.logo, new.siret, new.raison, new.commentaire); \
	INSERT INTO societe_avec_profils(authmodif, datemodif, datecrea, authcrea, codecrea, ref_statut, codemodif, ref_societe, ref_profil_societe) \
	VALUES (new.authmodif, new.datemodif, new.datecrea, new.authcrea, new.codecrea, new.ref_statut, new.codemodif, new.uid, (SELECT uid FROM profil_societe WHERE nom='prospect')); \
); \
CREATE RULE rule_prospect_delete AS \
ON DELETE TO view_prospect DO INSTEAD ( \
	DELETE FROM societe_avec_profils WHERE ref_societe=(SELECT uid FROM societe WHERE uid=old.uid); \
	DELETE FROM societe WHERE uid=old.uid; \
); \
CREATE RULE rule_prospect_update AS \
ON UPDATE TO view_prospect DO INSTEAD ( \
	UPDATE societe SET \
	authmodif = new.authmodif, \
	datemodif = coalesce(new.datemodif, now()), \
	datecrea = new.datecrea, \
	nom = new.nom, \
	authcrea = new.authcrea, \
	codecrea = new.codecrea, \
	ref_statut = new.ref_statut, \
	codemodif = new.codemodif, \
	email = new.email, \
	cp = new.cp, \
	adresse1 = new.adresse1, \
	tel2 = new.tel2, \
	adresse2 = new.adresse2, \
	ville = new.ville, \
	tel = new.tel, \
	scan_rib = new.scan_rib, \
	fax = new.fax, \
	web = new.web, \
	code = new.code , \
	ue_tva = new.ue_tva, \
	logo = new.logo, \
	siret = new.siret, \
	siren = new.siren, \
	raison = new.raison, \
	commentaire = new.commentaire \
	WHERE uid = new.uid; \
); \
COMMIT ;"
}

################################################
#                                              #
#      ECRIVEZ ICI LES PATCHES SOUHAITES       #
#        PimenTech ne supporte que la          #
#        version entierement patchée !         #
#                                              #
################################################

patch211

log "Patch 2.1 done."