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

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

log "Patch 2.0 start..."

# Informations professionnelles de tel et fax dans 'employe'
patch201() {
	echo -e "\t[PATCH 201] Information professionnelle dans travail_pour"
	log "[PATCH 201] Information professionnelle dans travail_pour"
	exec_sql "BEGIN ; \
ALTER TABLE travail_pour ADD COLUMN tel_pro text ; \
ALTER TABLE travail_pour ADD COLUMN fax_pro text ; \
INSERT INTO structchamp (ref_structtable, nom, denomination, rang, ref_type_edition) VALUES ((SELECT uid FROM structtable WHERE nom='travail_pour'), 'tel_pro', 'téléphone professionnel', 25, 11) ; \
INSERT INTO structchamp (ref_structtable, nom, denomination, rang, ref_type_edition) VALUES ((SELECT uid FROM structtable WHERE nom='travail_pour'), 'fax_pro', 'fax professionnel', 26, 11); \
COMMIT; "
}

# Migre les num de tel et fax professionnels de 'personne_physique' vers 'employe'
patch202() {
	echo -e "\t[PATCH 202] Migre les num de tel professionnels"
	log "[PATCH 202] Migre les num de tel professionnels"
	exec_sql "BEGIN ; \
UPDATE travail_pour SET tel_pro=(SELECT tel FROM personne_physique WHERE uid=travail_pour.ref_personne_physique) ; \
UPDATE personne_physique SET tel=null WHERE EXISTS (SELECT ref_personne_physique FROM travail_pour WHERE ref_personne_physique=personne_physique.uid) ; \
COMMIT ;"
	exec_sql "BEGIN ; \
UPDATE travail_pour SET fax_pro=(SELECT fax FROM personne_physique WHERE uid=travail_pour.ref_personne_physique) ; \
UPDATE personne_physique SET fax=null WHERE EXISTS (SELECT ref_personne_physique FROM travail_pour WHERE ref_personne_physique=personne_physique.uid) ; \
COMMIT ;"
}

# Efface les scan_rib de magasin et organisme
patch203() {
	echo -e "\t[PATCH 203] Efface les scan_rib de magasin et organisme"
	log "[PATCH 203] Efface les scan_rib de magasin et organisme"
	exec_sql "BEGIN ; \
DELETE FROM structchamp WHERE nom='scan_rib' AND ref_structtable=(SELECT uid from structtable WHERE nom='magasin') ; \
DELETE FROM structchamp WHERE nom='scan_rib' AND ref_structtable=(SELECT uid from structtable WHERE nom='organisme') ; \
COMMIT ;"
}

# Efface le fax de personne_physique
patch204() {
	echo -e "\t[PATCH 204] Efface le fax de personne_physique"
	log "[PATCH 204] Efface le fax de personne_physique"
	exec_sql "DELETE FROM structchamp WHERE nom='fax' AND ref_structtable=(SELECT uid from structtable WHERE nom='personne_physique') ;"
}

# Corrige le BUG de la date_paiement dans charge réglée
patch205() {
	echo -e "\t[PATCH 205] Corrige le BUG de la date_paiement dans charge réglée"
	log "[PATCH 205] Corrige le BUG de la date_paiement dans charge réglée"
	exec_sql "BEGIN ; \
DROP RULE rule_charge_rapide_insert ; \
CREATE RULE rule_charge_rapide_insert AS ON INSERT TO view_charge_rapide DO INSTEAD ( \
    INSERT INTO charge (uid, authmodif, datemodif, datecrea, authcrea, codecrea, ref_statut, codemodif, montant, ref_devise, ref_organisme, ref_type_charge, ref_view_mes_societes, commentaire, date_paiement) VALUES (new.uid, new.authmodif, new.datemodif, new.datecrea, new.authcrea, new.codecrea, new.ref_statut, new.codemodif, new.montant, new.ref_devise, new.ref_organisme, new.ref_type_charge, new.ref_view_mes_societes, new.commentaire, date('now')) ; \
   INSERT INTO reglement_charge (authmodif, datemodif, datecrea, authcrea, codecrea, ref_statut, codemodif, montant, ref_reglement, ref_charge) VALUES (new.authmodif, new.datemodif, new.datecrea, new.authcrea, new.codecrea, new.ref_statut, new.codemodif, new.montant, nextval('object_uid_seq'), new.uid) ; \
    INSERT INTO reglement (uid, authmodif, datemodif, datecrea, authcrea, codecrea, ref_statut, codemodif, montant, ref_devise, scan_cheque, numero_cheque, date_encaissement, ref_mode_paiement, date_emission) VALUES ((SELECT ref_reglement FROM reglement_charge WHERE ref_charge=new.uid), new.authmodif, new.datemodif, new.datecrea, new.authcrea, new.codecrea, new.ref_statut, new.codemodif, new.montant, new.ref_devise, new.scan_cheque, new.numero_cheque, date('now'), new.ref_mode_paiement, date('now')) ; ); \
COMMIT ;"
}

# Corrige le BUG de ref_mode_paiement dans achat rapide
patch206() {
	echo -e "\t[PATCH 206] Corrige le BUG de ref_mode_paiement dans achar rapide"
	log "[PATCH 206] Corrige le BUG de ref_mode_paiement dans achar rapide"
	exec_sql "BEGIN ; \
DROP VIEW view_achat_rapide ; \
CREATE VIEW view_achat_rapide AS SELECT a.*, r.uid AS ref_reglement, ra.uid AS ref_reglement_achat, r.scan_cheque, r.numero_cheque, r.ref_mode_paiement from achat a, reglement_achat ra, reglement r WHERE ra.ref_achat=a.uid AND ra.ref_reglement=r.uid ; \
CREATE RULE rule_achat_rapide_insert AS ON INSERT TO view_achat_rapide DO INSTEAD (	\
    INSERT INTO achat (uid, authmodif, datemodif, datecrea, authcrea, codecrea, ref_statut, codemodif, montant, ref_devise, reception_prevue, ref_type_achat, montant_ht, no_facture, reception, description, ref_view_mes_societes, ref_view_fournisseur) VALUES (new.uid, new.authmodif, new.datemodif, new.datecrea, new.authcrea, new.codecrea, new.ref_statut, new.codemodif, new.montant, new.ref_devise, new.reception_prevue, new.ref_type_achat, new.montant_ht, new.no_facture, new.reception, new.description, new.ref_view_mes_societes, new.ref_view_fournisseur) ; \
    INSERT INTO reglement_achat (authmodif, datemodif, datecrea, authcrea, codecrea, ref_statut, codemodif, montant, ref_reglement, ref_achat)	VALUES (new.authmodif, new.datemodif, new.datecrea, new.authcrea, new.codecrea, new.ref_statut, new.codemodif, new.montant, nextval('object_uid_seq'), new.uid) ; \
    INSERT INTO reglement (uid, authmodif, datemodif, datecrea, authcrea, codecrea, ref_statut, codemodif, montant, ref_devise, scan_cheque, numero_cheque, date_encaissement, date_emission,  ref_mode_paiement)	VALUES ((SELECT ref_reglement FROM reglement_achat WHERE ref_achat=new.uid), new.authmodif, new.datemodif, new.datecrea, new.authcrea, new.codecrea, new.ref_statut, new.codemodif, new.montant, new.ref_devise, new.scan_cheque, new.numero_cheque, date('now'), date('now'), new.ref_mode_paiement); ) ; \
CREATE RULE rule_achat_rapide_delete AS ON DELETE TO view_achat_rapide DO INSTEAD ( \
    DELETE FROM achat WHERE uid=old.uid ; \
    DELETE FROM reglement WHERE uid=old.ref_reglement ; \
    DELETE FROM reglement_achat WHERE ref_achat=old.uid ; ) ; \
CREATE RULE rule_achat_rapide_update AS \
ON UPDATE TO view_achat_rapide DO INSTEAD ( \
	UPDATE achat SET \
	authmodif = new.authmodif, \
	datemodif = coalesce(new.datemodif, date('now')), \
	datecrea = new.datecrea, \
	authcrea = new.authcrea, \
	codecrea = new.codecrea, \
	ref_statut = new.ref_statut, \
	codemodif = new.codemodif,  \
	montant = new.montant,  \
	ref_devise = new.ref_devise, \
	reception_prevue = new.reception_prevue, \
	ref_type_achat = new.ref_type_achat, \
	montant_ht = new.montant_ht, \
	no_facture = new.no_facture, \
	reception = new.reception, \
	description = new.description, \
	ref_view_mes_societes = new.ref_view_mes_societes, \
	ref_view_fournisseur = new.ref_view_fournisseur \
	WHERE uid = old.uid; \
	UPDATE reglement SET \
	authmodif = new.authmodif, \
	datemodif = coalesce(new.datemodif, date('now')), \
	datecrea = new.datecrea, \
	authcrea = new.authcrea, \
	codecrea = new.codecrea, \
	ref_statut = new.ref_statut, \
	codemodif = new.codemodif,  \
	montant = new.montant,  \
	ref_devise = new.ref_devise, \
	scan_cheque = new.scan_cheque, \
	numero_cheque = new.numero_cheque, \
	date_encaissement = date('now'), \
	date_emission = date('now'), \
	ref_mode_paiement = new.ref_mode_paiement \
	WHERE uid = (SELECT ref_reglement FROM reglement_achat WHERE ref_achat=old.uid); \
	UPDATE reglement_achat SET \
	authmodif = new.authmodif, \
	datemodif = coalesce(new.datemodif, date('now')), \
	datecrea = new.datecrea, \
	authcrea = new.authcrea, \
	codecrea = new.codecrea, \
	ref_statut = new.ref_statut, \
	codemodif = new.codemodif,  \
	montant = new.montant \
	WHERE ref_achat = old.uid; \
); \
COMMIT ;"
}

# Creation des intitule de facture
patch207() {
	echo -e "\t[PATCH 207] Creation des intitule de facture"
	log "[PATCH 207] Creation des intitule de facture"
	exec_sql "BEGIN ; \
ALTER TABLE facture ADD COLUMN intitule text ; \
DROP VIEW view_facture_extend; \
CREATE VIEW view_facture_extend AS SELECT f.uid AS uid, f.id, f.codecrea, f.intitule, f.authcrea, f.datecrea, f.codemodif, f.authmodif, f.datemodif, f.ref_statut, f.montant_ht, f.montant_ttc, f.date_emission, f.date_echeance, f.description, vc.uid AS ref_view_client, dv.uid AS ref_devise, d.uid AS ref_devis, d.commentaire, round((f.montant_ttc-coalesce((SELECT sum(rf.montant*dv2.taux_euro) FROM reglement r, reglement_facture rf, statut st, devise dv2 WHERE rf.ref_reglement=r.uid AND r.ref_statut=st.uid AND f.ref_statut=st.uid AND rf.ref_statut=st.uid AND d.ref_statut=st.uid AND st.nom='valide' AND rf.ref_facture=f.uid AND dv2.uid=r.ref_devise), 0)::float8/dv.taux_euro-0.001)::numeric, 2)::float8 AS du FROM facture f, view_client vc, devis d LEFT JOIN devise dv ON (dv.uid=d.ref_devise) WHERE d.ref_view_client=vc.uid AND f.ref_devis=d.uid; \
INSERT into structchamp (ref_structtable, nom, denomination, rang, ref_type_edition) values ((select uid from structtable where nom='facture'), 'intitule', 'intitulé', 10, (select uid from type_edition where nom='text')); \
COMMIT ;"
}


# Ajout du prix d'achat d'un article
patch208() {
	intit="Ajout prix d'achat des articles"
	echo -e "\t[PATCH 208] $intit"
	log "$intit"
	exec_sql "BEGIN; \
ALTER TABLE article ADD COLUMN pu_achat float8 ; \
INSERT INTO structchamp (ref_structtable, nom, denomination, rang, ref_type_edition) values ((select uid from structtable where nom='article'), 'pu_achat', 'prix d''achat', 25, 10); \
COMMIT ;"
}


# Correction bug ref_mode_paiement sur view_achat_rapide
patch209() {
	intit="[PÄTCH 209] Correction bug ref_mode_paiement sur view_achat_rapide"
	echo -e "\t$intit"
	log "$intit"
	exec_sql "BEGIN ; \
INSERT INTO structchamp (nom, denomination, rang, ref_type_edition, table_reference, ref_structtable) values ('ref_mode_paiement', 'mode de paiement', 15, 17, 20, 23); \
COMMIT ;"
}

# Rajoute pied de page
patch2010 () {
	intit="[PATCH 2010] Rajoute pied de page"
	echo -e "\t$intit"
	log "$intit"
	exec_sql "BEGIN ; \
ALTER TABLE societe ADD COLUMN pied_page text; \
INSERT INTO structchamp (ref_structtable, nom, denomination, rang, ref_type_edition) values ((SELECT uid FROM structtable WHERE nom='view_mes_societes'), 'pied_page', 'pied de page', 92, 14); \
DROP VIEW view_mes_societes; \
CREATE VIEW view_mes_societes 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='mes_societes'); \
update structtable set table_mere=(select uid from structtable where nom='societe') where nom='view_mes_societes'; \
CREATE RULE rule_mes_societes_insert AS \
ON INSERT TO view_mes_societes 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, pied_page) \
	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, new.pied_page); \
	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='mes_societes')); \
); \
CREATE RULE rule_mes_societes_delete AS \
ON DELETE TO view_mes_societes 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_mes_societes_update AS \
ON UPDATE TO view_mes_societes 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, \
	pied_page = new.pied_page \
	WHERE uid = new.uid; \
); \
COMMIT ;"
}

# Enleve le champ 'raison'
patch2011() {
	intit="[PATCH 2011] Enleve le champ raison"
	echo -e "\t$intit"
	log "$intit"
	exec_sql "BEGIN ; \
DELETE FROM structchamp WHERE nom='raison' AND ref_structtable=(SELECT uid from structtable WHERE nom='societe') ; \
DELETE FROM structchamp WHERE nom='raison' AND ref_structtable=(SELECT uid from structtable WHERE nom='view_mes_societes') ; \
DELETE FROM structchamp WHERE nom='raison' AND ref_structtable=(SELECT uid from structtable WHERE nom='view_client') ; \
DELETE FROM structchamp WHERE nom='raison' AND ref_structtable=(SELECT uid from structtable WHERE nom='view_fournisseur') ; \
UPDATE structchamp SET denomination='raison' WHERE nom='nom' AND ref_structtable=(SELECT uid from structtable WHERE nom='societe') ; \
UPDATE structchamp SET denomination='raison' WHERE nom='nom' AND ref_structtable=(SELECT uid from structtable WHERE nom='view_mes_societes') ; \
UPDATE structchamp SET denomination='raison' WHERE nom='nom' AND ref_structtable=(SELECT uid from structtable WHERE nom='view_client') ; \
UPDATE structchamp SET denomination='raison' WHERE nom='nom' AND ref_structtable=(SELECT uid from structtable WHERE nom='view_fournisseur') ; \
COMMIT ;"
}

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

patch201
patch202
patch203
patch204
patch205
patch206
patch207
patch208
patch209
patch2010
patch2011

log "Patch 2.0 done."