-- fichier de migration create table bisintervenant ( prenom text , datenaissance date , telmobile text , email text , nosecu text , dateentree date , datesortie date ) inherits (personne); insert into bisintervenant (adr1,adr2,cp,ville,tel,fax,nom,uid,prenom,datenaissance,telmobile,email,nosecu,dateentree,datesortie) select adresse,'',cp,ville,telfixe,'',nom,uid,prenom,datenaissance,telmobile,email,nosecu,dateentree,datesortie from intervenant; alter table intervenant rename to oldintervenant ; alter table bisintervenant rename to intervenant ; delete from structintervenant ; insert into structintervenant select * from structpersonne ; INSERT INTO "structintervenant" ("champ","denom","editable","rang","isoid","type","defaut") VALUES ('prenom','Prenom',1,1,'f','text',NULL,nextId()); INSERT INTO "structintervenant" ("champ","denom","editable","rang","isoid","type","defaut") VALUES ('telmobile','Tel (mobile)',1,5,'f','text',NULL,nextId()); INSERT INTO "structintervenant" ("champ","denom","editable","rang","isoid","type","defaut") VALUES ('email','E-mail',1,7,'f','text',NULL,nextId()); INSERT INTO "structintervenant" ("champ","denom","editable","rang","isoid","type","defaut") VALUES ('datenaissance','Date de naissance',1,8,'f','date',NULL,nextId()); INSERT INTO "structintervenant" ("champ","denom","editable","rang","isoid","type","defaut") VALUES ('nosecu','No de Secu',1,9,'f','text',NULL,nextId()); INSERT INTO "structintervenant" ("champ","denom","editable","rang","isoid","type","defaut") VALUES ('dateentree','Date Entree',1,10,'f','date','',nextId()); INSERT INTO "structintervenant" ("champ","denom","editable","rang","isoid","type","defaut") VALUES ('datesortie','Date Sortie',1,11,'f','date','',nextId()); drop table oldintervenant ; create table rights ( uid int4, structtable oid, intervenant oid, read int4, write int4 ); create table structrights () inherits (structtable) ; INSERT INTO "structrights" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('structtable','Table',1,1,'f','oids',NULL,nextId()); INSERT INTO "structrights" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('intervenant','Intervenant',1,5,'f','oid',NULL,nextId()); INSERT INTO "structrights" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('read','Lecture',1,7,'f','int4',NULL,nextId()); INSERT INTO "structrights" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('write','Ecriture',1,8,'f','int4',NULL,nextId()); INSERT INTO "structrights" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('uid','pg_id',1,99,'f','int4',NULL,nextId()); alter table structtable add column uid int4; update structtable set uid=nextId(); insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('rights','Droits',1,14,'f','Config',NULL,nextId()); create table structstructtable () inherits (structtable) ; INSERT INTO "structstructtable" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('champ',"Champ",1,0,'f','text',NULL,nextId()); INSERT INTO "structstructtable" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('denom','',1,0,'f','text',NULL,nextId()); INSERT INTO "structstructtable" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('editable','',1,0,'f','text',NULL,nextId()); INSERT INTO "structstructtable" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('rang','',1,0,'f','text',NULL,nextId()); INSERT INTO "structstructtable" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('isoid','',1,0,'f','text',NULL,nextId()); INSERT INTO "structstructtable" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('type','',1,0,'f','text',NULL,nextId()); INSERT INTO "structstructtable" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('defaut','',1,0,'f','text',NULL,nextId()); INSERT INTO "structstructtable" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('uid','pg_id',0,0,'f','int4',NULL,nextId()); -- Comment this if you dont want to allow all by default : INSERT INTO rights (intervenant,structtable,read,write,uid) SELECT i.uid,s.uid,1,1,nextId() from intervenant i,structtable s; ------------ -- DONE ----------- -- Customizing Printable paper create table maboite ( logo text, headerdoc text, footerdoc text ) inherits(societe) ; ------------ -- DONE ----------- create table structmaboite () inherits (structtable); insert into structmaboite select * from structsociete ; INSERT INTO "structmaboite" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('logo','Logo',1,1,'f','file','',nextId()); INSERT INTO "structmaboite" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('headerdoc','Entête',1,2,'f','text','',nextId()); INSERT INTO "structmaboite" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('footerdoc','Pied de page',1,3,'f','text','',nextId()); insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('maboite','Ma Société',1,14,'f','Config',NULL,nextId()); ------------ -- DONE ----------- alter table personne add column email text ; alter table societe add column email text ; alter table societe add column typesociete oid ; alter table societe add column capital text ; alter table client add column email text ; alter table client add column typesociete oid ; alter table client add column capital text ; alter table maboite add column email text ; alter table maboite add column typesociete oid ; alter table maboite add column capital text ; alter table fournisseur add column email text ; alter table fournisseur add column typesociete oid ; alter table fournisseur add column capital text ; ------------ -- DONE ----------- alter table structpersonne add column uid int4; insert into "structpersonne" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('email','Email',1,1,'f','text','',nextId()); insert into "structmaboite" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('email','Email',1,1,'f','text','',nextId()); insert into "structmaboite" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('typesociete','Type Soc.',1,1,'f','oid','',nextId()); insert into "structmaboite" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('capital','Capital',1,1,'f','text','',nextId()); ------------ -- DONE ----------- alter table structclient add column uid int4; alter table structfournisseur add column uid int4; alter table structsociete add column uid int4; insert into "structclient" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('email','Email',1,1,'f','text','',nextId()); insert into "structclient" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('typesociete','Type Soc.',1,1,'f','oid','',nextId()); insert into "structclient" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('capital','Capital',1,1,'f','text','',nextId()); insert into "structsociete" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('email','Email',1,1,'f','text','',nextId()); insert into "structsociete" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('typesociete','Type Soc.',1,1,'f','oid','',nextId()); insert into "structsociete" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('capital','Capital',1,1,'f','text','',nextId()); insert into "structfournisseur" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('email','Email',1,1,'f','text','',nextId()); insert into "structfournisseur" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('typesociete','Type Soc.',1,1,'f','oid','',nextId()); insert into "structfournisseur" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('capital','Capital',1,1,'f','text','',nextId()); ------------ -- DONE ----------- create table typesociete ( nom text, nomlong text, uid int4 ); create table structtypesociete () inherits (structtable); insert into "structtypesociete" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('nom','Abrev.',1,1,'f','text','',nextId()); insert into "structtypesociete" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('nomlong','Nom Long',1,1,'f','text','',nextId()); insert into "structtypesociete" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('uid','pg_id',1,0,'f','int4','',nextId()); insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('typesociete','Type de Societe',1,14,'f','Config',NULL,nextId()); ------------ -- DONE ----------- create table statut ( nom text, uid int4 ); create table structstatut () inherits (structtable); insert into "structstatut" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('nom','Type',1,1,'f','text','',nextId()); insert into "structstatut" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('uid','pg_id',0,1,'f','int4','',nextId()); insert into statut values ('client',nextId()); insert into statut values ('admin',nextId()); insert into statut values ('intervenant',nextId()); insert into statut values ('comptable',nextId()); ------------ -- DONE ----------- insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('statut','Type Statut',1,14,'f','Config',NULL,nextId()); create table tempuserid as select id,pwd,personne,(select statut.uid where statut.nom=userid.statut) as statut,uid from userid ; drop table userid; alter table tempuserid rename to userid; update structuserid set type='oid' where champ='statut'; ------------ -- DONE ----------- --banque : create table banque ( code_banque text, code_guichet text ) inherits (societe); create table structbanque as select * from structsociete ; insert into "structbanque" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('code_banque','Banque',1,1,'f','text','',nextId()); insert into "structbanque" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('code_guichet','Guichet',1,1,'f','text','',nextId()); insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('banque','Banque',1,14,'f','Compta',NULL,nextId()); create table compte_banque ( uid int4, rib text, nocompte text, libelle text, compte text, banque oid ); create table structcompte_banque () inherits (structtable); ------------ -- DONE ----------- insert into structcompte_banque ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('uid','pg_id',0,1,'f','int4','',nextId()); insert into structcompte_banque ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('rib','RIB',1,1,'f','text','',nextId()); insert into structcompte_banque ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('nocompte','No Compte',1,1,'f','text','',nextId()); insert into structcompte_banque ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('libelle','Libellé',1,1,'f','text','',nextId()); insert into structcompte_banque ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('compte','Compte',1,1,'f','text','',nextId()); insert into structcompte_banque ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('banque','Banque',1,1,'f','oid','',nextId()); insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('compte_banque','Comptes',1,14,'f','Compta',NULL,nextId()); ------------ -- DONE ----------- create table mouvement_banque ( devise oid, F text, X text, libelle text, montant_franc float8, montant_euro float8, date date, date_valeur date, uid int4, compte_banque oid ); create table structmouvement_banque () inherits (structtable); ------------ -- DONE ----------- insert into structmouvement_banque ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('uid','pg_id',0,1,'f','int4','',nextId()); insert into structmouvement_banque ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('devise','Devise',1,1,'f','oid','',nextId()); insert into structmouvement_banque ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('F','F',1,1,'f','text','',nextId()); insert into structmouvement_banque ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('X','X',1,1,'f','text','',nextId()); insert into structmouvement_banque ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('libelle','Libelle',1,1,'f','text','',nextId()); insert into structmouvement_banque ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('montant_franc','Montant (FR)',1,1,'f','float8','',nextId()); insert into structmouvement_banque ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('montant_euro','Montant (EUR)',1,1,'f','float8','',nextId()); insert into structmouvement_banque ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('date','Date',1,1,'f','date','',nextId()); insert into structmouvement_banque ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('date_valeur','Date Valeur',1,1,'f','date','',nextId()); insert into structmouvement_banque ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('compte_banque','Compte',1,1,'f','oid','',nextId()); insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('mouvement_banque','Mouvements',1,14,'f','Compta',NULL,nextId()); ------------ -- DONE ----------- create table devise ( nom text, abbrev text, nbeuros float8, uid int4 ); create table structdevise () inherits (structtable); insert into structdevise ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('uid','pg_id',0,99,'f','oid','',nextId()); insert into structdevise ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('nom','Nom',1,1,'f','text','',nextId()); insert into structdevise ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('abbrev','Symbole',1,1,'f','text','',nextId()); insert into structdevise ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('nbeuros','Valeur EUR',1,1,'f','float8','',nextId()); insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('devise','Devises',1,14,'f','Compta',NULL,nextId()); UPDATE structdevise set rang=0 where champ='abbrev'; UPDATE structcompte_banque set rang=0 where champ='libelle'; UPDATE structmouvement_banque set rang=rang+2; UPDATE structmouvement_banque set rang=0 where champ='date'; UPDATE structmouvement_banque set rang=1 where champ='date_valeur'; ------------ -- DONE ----------- alter table structachat add column uid int4 ; alter table structcharges add column uid int4 ; alter table structch_sociales add column uid int4 ; alter table structreglement add column uid int4 ; alter table achat add column rapproche int4 ; alter table charges add column rapproche int4 ; alter table ch_sociales add column rapproche int4 ; alter table reglement add column rapproche int4 ; insert into structachat ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('rapproche','Rapproche',1,1,'f','bool','',nextId()); insert into structcharges ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('rapproche','Rapproche',1,1,'f','bool','',nextId()); insert into structch_sociales ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('rapproche','Rapproche',1,1,'f','bool','',nextId()); insert into structreglement ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('rapproche','Rapproche',1,1,'f','bool','',nextId()); ------------ -- DONE ----------- update achat set rapproche=0; update charges set rapproche=0; update ch_sociales set rapproche=0; update reglement set rapproche=0; ------------ -- DONE ----------- create table rapprochement ( uid int4 default nextId(), mouvement_banque oid, achat oid, charges oid, ch_sociales oid, reglement oid ); ------------ -- DONE ----------- INSERT INTO "structtable" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('rapprochement','Rapprochements',1,0,'f','Compta',NULL,nextId()); create table structrapprochement () inherits (structtable); insert into structrapprochement ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('uid','pg_id',0,1,'f','int4','',nextId()); insert into structrapprochement ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('mouvement_banque','Mouvement',0,1,'f','oid','',nextId()); insert into structrapprochement ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('achat','Achat',0,1,'f','int4','',nextId()); insert into structrapprochement ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('charges','Charge',0,1,'f','int4','',nextId()); insert into structrapprochement ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('ch_sociales','Charge Soc.',0,1,'f','int4','',nextId()); insert into structrapprochement ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('reglement','Reglement',0,1,'f','int4','',nextId()); ------------ -- DONE ----------- alter table rapprochement add column salaire oid; insert into structrapprochement ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('salaire','Salaire',0,1,'f','int4','',nextId()); ------------ -- DONE ----------- -- Insertion de devises : alter table factures add column devise oid; alter table structfactures add column uid int4 ; insert into structfactures ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('devise','Devise',0,1,'f','oid','',nextId()); alter table devis add column devise oid; alter table structdevis add column uid int4 ; insert into structdevis ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('devise','Devise',0,1,'f','oid','',nextId()); begin ; update factures set devise = (select uid from devise where abbrev='FRF') ; update devis set devise = (select uid from devise where abbrev='FRF') ; commit ; alter table achat add column devise oid; insert into structachat ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('devise','Devise',0,1,'f','oid','',nextId()); alter table charges add column devise oid; insert into structcharges ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('devise','Devise',0,1,'f','oid','',nextId()); alter table ch_sociales add column devise oid; insert into structch_sociales ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('devise','Devise',0,1,'f','oid','',nextId()); alter table salaire add column devise oid; alter table structsalaire add column uid int4 ; insert into structsalaire ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('devise','Devise',0,1,'f','oid','',nextId()); ------------ -- DONE ----------- begin ; update achat set devise = (select uid from devise where abbrev='FRF') ; update salaire set devise = (select uid from devise where abbrev='FRF') ; update charges set devise = (select uid from devise where abbrev='FRF') ; update ch_sociales set devise = (select uid from devise where abbrev='FRF') ; update structachat set editable=1 where champ='devise' ; update structsalaire set editable=1 where champ='devise' ; update structcharges set editable=1 where champ='devise' ; update structch_sociales set editable=1 where champ='devise' ; commit ; ------------ -- DONE ----------- alter table reglement add column devise oid; insert into structreglement ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('devise','Devise',1,1,'f','oid','',nextId()); update reglement set devise = (select uid from devise where abbrev='FRF') ; ------------ -- DONE ----------- create table object ( uid SERIAL, authcrea oid, authmodif oid, datecrea date, datemodif date ); create table structobject () inherits (structtable); insert into structobject ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('uid','pg_id',0,1,'f','int4','',nextId()); insert into structobject ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('authcrea','Auteur',1,1,'f','oid','',nextId()); insert into structobject ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('authmodif','Modifieur',1,1,'f','oid','',nextId()); insert into structobject ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('datecrea','Creation',1,1,'f','date','',nextId()); insert into structobject ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('datemodif','Last Modif.',1,1,'f','date','',nextId()); create table compte ( id text, intitule text, credit float8, debit float8, solde float8 ) inherits (object); create table structcompte () inherits (structtable); insert into structcompte select * from structobject; insert into structcompte ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('id','No',1,1,'f','text','',nextId()); insert into structcompte ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('intitule','Intitule',1,1,'f','text','',nextId()); insert into structcompte ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('credit','Credit',1,1,'f','float8','',nextId()); insert into structcompte ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('debit','Debit',1,1,'f','float8','',nextId()); insert into structcompte ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('solde','Solde',1,1,'f','float8','',nextId()); create table ecriture ( action text CHECK (action = 'credite' OR action = 'debite'), compte oid, valeur float8, intitule text, date date, groupe_ecriture oid ) inherits (object); create table structecriture () inherits (structtable); insert into structecriture select * from structobject; insert into structecriture ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('compte','Compte',1,1,'f','oid','',nextId()); insert into structecriture ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('action','Action',1,1,'f','text','',nextId()); insert into structecriture ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('valeur','Valeur',1,1,'f','float8','',nextId()); insert into structecriture ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('intitule','Intitule',1,1,'f','text','',nextId()); insert into structecriture ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('date','Date',1,1,'f','date','',nextId()); insert into structecriture ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('groupe_ecriture','Groupe',1,1,'f','oid','',nextId()); create table brouillard ( action text CHECK (action = 'credite' OR action = 'debite'), compte oid, valeur float8, intitule text, date date, groupe_ecriture oid ) inherits (object); create table structbrouillard () inherits (structtable); insert into structbrouillard select * from structobject; insert into structbrouillard ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('compte','Compte',1,1,'f','oid','',nextId()); insert into structbrouillard ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('action','Action',1,1,'f','text','',nextId()); insert into structbrouillard ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('valeur','Valeur',1,1,'f','float8','',nextId()); insert into structbrouillard ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('intitule','Intitule',1,1,'f','text','',nextId()); insert into structbrouillard ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('date','Date',1,1,'f','date','',nextId()); insert into structbrouillard ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('groupe_ecriture','Groupe',1,1,'f','text','',nextId()); create table groupe_ecriture ( libelle text ) inherits (object); create table structgroupe_ecriture () inherits (structtable); insert into structgroupe_ecriture select * from structobject; insert into structgroupe_ecriture ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('libelle','Titre',1,1,'f','text','',nextId()); ----------------------- alter table personne add column compte oid ; alter table societe add column compte oid ; alter table client add column compte oid ; alter table maboite add column compte oid ; alter table fournisseur add column compte oid ; alter table organisme add column compte oid ; alter table banque add column compte oid ; ALTER TABLE structorganisme ADD column uid int4; insert into "structpersonne" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('compte','Compte',1,1,'f','oid','',nextId()); insert into "structbanque" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('compte','Compte',1,1,'f','oid','',nextId()); insert into "structmaboite" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('compte','Compte',1,1,'f','oid','',nextId()); insert into "structclient" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('compte','Compte',1,1,'f','oid','',nextId()); insert into "structsociete" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('compte','Compte',1,1,'f','oid','',nextId()); insert into "structfournisseur" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('compte','Compte',1,1,'f','oid','',nextId()); insert into "structorganisme" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('compte','Compte',1,1,'f','oid','',nextId()); -------------- -- DONE ----- -------------- insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('compte','Comptes',1,14,'f','Compta',NULL,nextId()); insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('ecriture','Mvt Comptes',1,14,'f','Compta',NULL,nextId()); insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('brouillard','Brouillard',1,14,'f','Compta',NULL,nextId()); insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('groupe_ecriture','Mvt Compta.',1,14,'f','Compta',NULL,nextId()); INSERT into compte (authcrea,authmodif,datemodif,datecrea,id,intitule) select 0,0,'now','now','401'||nom,nom from fournisseur ; UPDATE fournisseur set compte=compte.uid where compte.intitule=nom ; INSERT into compte (authcrea,authmodif,datemodif,datecrea,id,intitule) select 0,0,'now','now','401'||nom,nom from organisme ; UPDATE organisme set compte=compte.uid where compte.intitule=nom ; INSERT into compte (authcrea,authmodif,datemodif,datecrea,id,intitule) select 0,0,'now','now','411'||nom,nom from client ; UPDATE client set compte=compte.uid where compte.intitule=nom ; INSERT into compte (authcrea,authmodif,datemodif,datecrea,id,intitule) select 0,0,'now','now','512'||nom,nom from banque ; UPDATE banque set compte=compte.uid where compte.intitule=nom ; ------------------------------------------------------------------------ -- REMPLIR ICI LES COMPTES AVEC LE FICHIER createCompte.sql DANS lib/ ------------------------------------------------------------------------ \i /home/manu/src/projets/interne/gestion/lib/createCompte.sql -- remplissage des comptes : insert into compte (authcrea,authmodif,datemodif,datecrea,id,intitule) values (0,0,'now','now','000','PimenTech'); ------------------------------------------------------------------------ -- REMPLIR ICI LES COMPTES AVEC LE FICHIER généré par mig2compta.pl DANS lib/ ------------------------------------------------------------------------ -------------- -- DONE ----- -------------- alter table achat add column groupe_ecriture oid; alter table factures add column groupe_ecriture oid; alter table reglement add column groupe_ecriture oid; insert into "structfactures" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('groupe_ecriture','Ecritures',1,1,'f','oid','',nextId()); insert into "structachat" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('groupe_ecriture','Ecritures',1,1,'f','oid','',nextId()); insert into "structreglement" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('groupe_ecriture','Ecritures',1,1,'f','oid','',nextId()); BEGIN; insert into groupe_ecriture (authcrea,authmodif,datemodif,datecrea,libelle) select 0,0,f.dateemission,f.dateemission,'FACTURE '||f.id from factures f where f.groupe_ecriture is null; update ecriture set groupe_ecriture=g.uid from groupe_ecriture g where g.libelle=intitule and groupe_ecriture is null;; insert into groupe_ecriture (authcrea,authmodif,datemodif,datecrea,libelle) select 0,0,e.date,e.date,e.intitule from ecriture e where e.intitule not in (select libelle from groupe_ecriture) group by e.date,e.intitule; update ecriture set groupe_ecriture=g.uid from groupe_ecriture g where g.libelle=intitule and g.datecrea=date and groupe_ecriture is null; update factures set groupe_ecriture=g.uid from groupe_ecriture g where g.libelle='FACTURE '||id ; update reglement set groupe_ecriture=g.uid from groupe_ecriture g , compte c, client cl, ecriture e where cl.uid=client and datepaye=g.datecrea and cl.compte=c.uid and e.groupe_ecriture=g.uid and e.compte=c.uid; COMMIT ; -------------- -- DONE ----- -------------- alter table groupe_ecriture add column valide int4; insert into "structgroupe_ecriture" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('valide','Valide',1,8,'f','bool','',nextId()); update groupe_ecriture set valide=1 where uid in (select groupe_ecriture from ecriture group by groupe_ecriture); update groupe_ecriture set valide=0 where uid in (select groupe_ecriture from brouillard group by groupe_ecriture); update groupe_ecriture set valide=2 where uid not in (select groupe_ecriture from ecriture group by groupe_ecriture) and uid not in (select groupe_ecriture from brouillard group by groupe_ecriture); alter table groupe_ecriture add column exercice int4; insert into "structgroupe_ecriture" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('exercice','Exercice',1,8,'f','annee','',nextId()); update groupe_ecriture set exercice=date_part('year',datecrea); -------------- -- DONE ----- -------------- alter table ecriture add column exercice int4 default date_part('year',date('now')); insert into "structecriture" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('exercice','Exercice',1,8,'f','annee','',nextId()); alter table brouillard add column exercice int4 default date_part('year',date('now')); insert into "structbrouillard" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('exercice','Exercice',1,8,'f','annee','',nextId()); UPDATE ecriture set exercice=date_part('year',date); UPDATE brouillard set exercice=date_part('year',date); -------------- -- DONE ----- -------------- -- lancer ici le chargement du fichier généré par migRglt4tva.pl -------------- -- DONE ----- -------------- alter table charges add column groupe_ecriture oid; insert into "structcharges" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('groupe_ecriture','Ecritures',1,1,'f','oid','',nextId()); -------------- -- DONE ----- -------------- drop table rapprochement; drop table structrapprochement; delete from structable where champ='rapprochement'; create table rapprochement ( uid SERIAL, mouvement_banque oid, ecriture oid ); INSERT INTO "structtable" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('rapprochement','Rapprochements',1,0,'f','Compta',NULL,nextId()); create table structrapprochement () inherits (structtable); insert into structrapprochement ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('uid','pg_id',0,1,'f','int4','',nextId()); insert into structrapprochement ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('mouvement_banque','Mouvement',1,1,'f','oid','',nextId()); insert into structrapprochement ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('ecriture','Ecritures',1,1,'f','oid','',nextId()); -- comptes d'associés alter table structintervenant add column uid int4; insert into compte (id,intitule,credit,debit,solde,datemodif,datecrea,authmodif,authcrea,uid) select '455' || i.nom,i.nom,'','','','now','now','','',nextId() from intervenant i; alter table intervenant add column compte int4; insert into "structintervenant" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('compte','Compte',1,1,'f','oid','',nextId()); update intervenant set compte=c.uid from compte c where c.intitule=nom; alter table salaire add column groupe_ecriture oid; insert into "structsalaire" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('groupe_ecriture','Ecritures',1,1,'f','oid','',nextId()); update structsalaire set isoid=false; -------------- -- DONE ----- -------------- alter table compte_banque add column compte oid; insert into "structcompte_banque" ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('compte','Compte',1,1,'f','oid','',nextId()); -------------- -- DONE ----- -------------- -- rajout d'un champ URL dans la table societe alter table societe add column url text; alter table client add column url text; insert into structsociete ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('url','URL',1,7,false,'text','',nextId()); insert into structclient ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('url','URL',1,7,false,'text','',nextId()); -- types d'action CRM create table typeaction ( nom text ) inherits (object); create table action_crm ( intervenant oid, contact oid, typeaction oid, date timestamp, reaction int4, commentaire text) inherits (object); create table structtypeaction () inherits (structtable); insert into structtypeaction select * from structobject; insert into structtypeaction ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('nom','Action',1,1,'f','text','',nextId()); create table structaction_crm () inherits (structtable); insert into structaction_crm select * from structobject; insert into structaction_crm ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('intervenant','Intervenant',1,0,'f','oid','',nextId()); insert into structaction_crm ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('contact','Contact',1,1,'f','oid','',nextId()); insert into structaction_crm ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('typeaction','Action',1,1,'f','oid','',nextId()); insert into structaction_crm ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('date','Date',1,2,'f','timestamp','',nextId()); insert into structaction_crm ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('reaction','Reaction',1,3,'f','bool','',nextId()); insert into structaction_crm ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('commentaire','Commentaire',1,4,'f','textA','',nextId()); insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('typeaction','Type Action',1,14,'f','G. Comm.',NULL,nextId()); insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('action_crm','Actions CRM',1,14,'f','G. Comm.',NULL,nextId()); UPDATE structtypeaction set rang=0 where champ='nom'; UPDATE structaction_crm set defaut='$pimuser' where champ='intervenant'; UPDATE structaction_crm set defaut='$contact' where champ='contact'; UPDATE structaction_crm set isoid='t' where champ='authcrea'; UPDATE structaction_crm set isoid='t' where champ='authmodif'; UPDATE structcontact set defaut = '$societe' where champ='societe'; -- champ fonction de contact create table fonction ( nom text ) inherits (object); alter table contact add fonction oid; insert into structcontact ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('fonction','Fonction',1,4,'f','oid','',nextId()); create table structfonction () inherits (structtable); insert into structfonction select * from structobject; insert into structfonction ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('nom','Fonction',1,1,'f','text','',nextId()); insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('fonction','Fonction',1,14,'f','Config',NULL,nextId()); UPDATE structfonction set rang=0 where champ='nom'; -------------- -- DONE ----- -------------- create table reaction ( nom text ) inherits (object); create table structreaction () inherits (structtable); insert into structreaction select * from structobject; insert into structreaction ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('nom','Reaction',1,1,'f','text','',nextId()); insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('reaction','Reaction',1,14,'f','Config',NULL,nextId()); UPDATE structreaction set rang=0 where champ='nom'; insert into reaction (nom) values ('PLUTOT POSITIF'); insert into reaction (nom) values ('POSITIF'); insert into reaction (nom) values ('TRES POSITIF'); insert into reaction (nom) values ('NEGATIF'); insert into reaction (nom) values ('PLUTOT NEGATIF'); insert into reaction (nom) values ('TRES NEGATIF'); create table todo_act ( nom text ) inherits (object); create table structtodo_act () inherits (structtable); insert into structtodo_act select * from structobject; insert into structtodo_act ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('nom','Todo_Act',1,1,'f','text','',nextId()); insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('todo_act','Todo_Act',1,14,'f','Config',NULL,nextId()); UPDATE structtodo_act set rang=0 where champ='nom'; insert into todo_act (nom) values ('A RAPPELER'); insert into todo_act (nom) values ('FIN DES CONTACTS'); insert into todo_act (nom) values ('EN ATTENTE'); update structaction_crm set type='oid' where champ='reaction'; alter table action_crm add column todo_act integer; insert into structaction_crm ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('todo_act','A Faire',1,14,'f','oid',0,nextId()); -------------- -- DONE ----- -------------- alter table structtable add column disp text; alter table structobject add column disp text; alter table structpersonne add column disp text; alter table structcompte add column disp text; alter table structbrouillard add column disp text; alter table structgroupe_ecriture add column disp text; alter table structrapprochement add column disp text; alter table structachat add column disp text; alter table structaction_crm add column disp text; alter table structreaction add column disp text; alter table structfonction add column disp text; alter table structtypeaction add column disp text; alter table structmaboite add column disp text; alter table structtypesociete add column disp text; alter table structstatut add column disp text; alter table structbanque add column disp text; alter table structmouvement_banque add column disp text; alter table structdevise add column disp text; alter table structfournisseur add column disp text; alter table structrapprochement add column disp text; alter table structintervenant add column disp text; alter table structtodo_act add column disp text; alter table structrdvstate add column disp text; alter table structdevis add column disp text; alter table structfactures add column disp text; alter table structreglement add column disp text; alter table structcontact add column disp text; alter table structconstantes add column disp text; alter table structsociete add column disp text; alter table structrelance add column disp text; alter table structlettre add column disp text; alter table structcompte_banque add column disp text; alter table structecriture add column disp text; alter table structorganisme add column disp text; alter table structtypepaiement add column disp text; alter table structuserid add column disp text; alter table structtypesalaire add column disp text; alter table structclient add column disp text; alter table structrendezvous add column disp text; alter table structjhomme add column disp text; alter table structsalaire add column disp text; alter table structch_sociales add column disp text; alter table structcharges add column disp text; alter table structrefrdv add column disp text; alter table structtypeactivite add column disp text; alter table structformule add column disp text; alter table structagenda add column disp text; alter table structrights add column disp text; update structobject set disp=champ where type='oid'; update structcompte set disp=champ where type='oid'; update structpersonne set disp=champ where type='oid'; update structbrouillard set disp=champ where type='oid'; update structgroupe_ecriture set disp=champ where type='oid'; update structrapprochement set disp=champ where type='oid'; update structachat set disp=champ where type='oid'; update structaction_crm set disp=champ where type='oid'; update structreaction set disp=champ where type='oid'; update structfonction set disp=champ where type='oid'; update structtypeaction set disp=champ where type='oid'; update structmaboite set disp=champ where type='oid'; update structtypesociete set disp=champ where type='oid'; update structstatut set disp=champ where type='oid'; update structbanque set disp=champ where type='oid'; update structmouvement_banque set disp=champ where type='oid'; update structdevise set disp=champ where type='oid'; update structfournisseur set disp=champ where type='oid'; update structrapprochement set disp=champ where type='oid'; update structintervenant set disp=champ where type='oid'; update structtodo_act set disp=champ where type='oid'; update structrdvstate set disp=champ where type='oid'; update structdevis set disp=champ where type='oid'; update structfactures set disp=champ where type='oid'; update structreglement set disp=champ where type='oid'; update structcontact set disp=champ where type='oid'; update structconstantes set disp=champ where type='oid'; update structsociete set disp=champ where type='oid'; update structrelance set disp=champ where type='oid'; update structlettre set disp=champ where type='oid'; update structcompte_banque set disp=champ where type='oid'; update structecriture set disp=champ where type='oid'; update structorganisme set disp=champ where type='oid'; update structtypepaiement set disp=champ where type='oid'; update structuserid set disp=champ where type='oid'; update structtypesalaire set disp=champ where type='oid'; update structclient set disp=champ where type='oid'; update structrendezvous set disp=champ where type='oid'; update structjhomme set disp=champ where type='oid'; update structsalaire set disp=champ where type='oid'; update structch_sociales set disp=champ where type='oid'; update structcharges set disp=champ where type='oid'; update structrefrdv set disp=champ where type='oid'; update structtypeactivite set disp=champ where type='oid'; update structformule set disp=champ where type='oid'; update structagenda set disp=champ where type='oid'; update structrights set disp=champ where type='oid'; update structobject set disp='personne' where champ='authcrea' or champ='authmodif'; update structcompte set disp='personne' where champ='authcrea' or champ='authmodif'; update structbrouillard set disp='personne' where champ='authcrea' or champ='authmodif'; update structgroupe_ecriture set disp='personne' where champ='authcrea' or champ='authmodif'; update structrapprochement set disp='personne' where champ='authcrea' or champ='authmodif'; update structachat set disp='personne' where champ='authcrea' or champ='authmodif'; update structaction_crm set disp='personne' where champ='authcrea' or champ='authmodif'; update structreaction set disp='personne' where champ='authcrea' or champ='authmodif'; update structfonction set disp='personne' where champ='authcrea' or champ='authmodif'; update structtypeaction set disp='personne' where champ='authcrea' or champ='authmodif'; update structmaboite set disp='personne' where champ='authcrea' or champ='authmodif'; update structtypesociete set disp='personne' where champ='authcrea' or champ='authmodif'; update structstatut set disp='personne' where champ='authcrea' or champ='authmodif'; update structbanque set disp='personne' where champ='authcrea' or champ='authmodif'; update structmouvement_banque set disp='personne' where champ='authcrea' or champ='authmodif'; update structdevise set disp='personne' where champ='authcrea' or champ='authmodif'; update structfournisseur set disp='personne' where champ='authcrea' or champ='authmodif'; update structrapprochement set disp='personne' where champ='authcrea' or champ='authmodif'; update structintervenant set disp='personne' where champ='authcrea' or champ='authmodif'; update structtodo_act set disp='personne' where champ='authcrea' or champ='authmodif'; update structrdvstate set disp='personne' where champ='authcrea' or champ='authmodif'; update structdevis set disp='personne' where champ='authcrea' or champ='authmodif'; update structfactures set disp='personne' where champ='authcrea' or champ='authmodif'; update structreglement set disp='personne' where champ='authcrea' or champ='authmodif'; update structcontact set disp='personne' where champ='authcrea' or champ='authmodif'; update structconstantes set disp='personne' where champ='authcrea' or champ='authmodif'; update structsociete set disp='personne' where champ='authcrea' or champ='authmodif'; update structrelance set disp='personne' where champ='authcrea' or champ='authmodif'; update structlettre set disp='personne' where champ='authcrea' or champ='authmodif'; update structcompte_banque set disp='personne' where champ='authcrea' or champ='authmodif'; update structecriture set disp='personne' where champ='authcrea' or champ='authmodif'; update structorganisme set disp='personne' where champ='authcrea' or champ='authmodif'; update structtypepaiement set disp='personne' where champ='authcrea' or champ='authmodif'; update structuserid set disp='personne' where champ='authcrea' or champ='authmodif'; update structtypesalaire set disp='personne' where champ='authcrea' or champ='authmodif'; update structclient set disp='personne' where champ='authcrea' or champ='authmodif'; update structrendezvous set disp='personne' where champ='authcrea' or champ='authmodif'; update structjhomme set disp='personne' where champ='authcrea' or champ='authmodif'; update structsalaire set disp='personne' where champ='authcrea' or champ='authmodif'; update structch_sociales set disp='personne' where champ='authcrea' or champ='authmodif'; update structcharges set disp='personne' where champ='authcrea' or champ='authmodif'; update structrefrdv set disp='personne' where champ='authcrea' or champ='authmodif'; update structtypeactivite set disp='personne' where champ='authcrea' or champ='authmodif'; update structformule set disp='personne' where champ='authcrea' or champ='authmodif'; update structagenda set disp='personne' where champ='authcrea' or champ='authmodif'; update structrights set disp='personne' where champ='authcrea' or champ='authmodif'; -------------- -- DONE ----- -------------- alter table salaire add typepaiement oid; alter table salaire add nocheque text; insert into structsalaire ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('typepaiement','Type Paiement',1,14,'f','oid',0,nextId()); insert into structsalaire ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('nocheque','Cheque No',1,14,'f','text',0,nextId()); -- integer ici le resultat du lancement de ./updateNochequeSalaire.pl -------------- -- DONE ----- -------------- create table img_chq ( image text, groupe_ecriture oid ) inherits (object); create table structimg_chq () inherits (structtable); insert into structimg_chq select * from structobject; insert into structimg_chq ("champ","denom","editable","rang","isoid","type","defaut","uid") VALUES ('image','Image',1,1,'f','file','',nextId()); insert into structimg_chq ("champ","denom","editable","rang","isoid","type","defaut","disp","uid") VALUES ('groupe_ecriture','Ecritures',1,1,'f','oid','','groupe_ecriture',nextId()); insert into structtable ("champ","denom","editable","rang","isoid","type","defaut","disp","uid") VALUES ('img_chq','Img. Chèque',1,14,'f','Config',NULL,'image',nextId()); -------------- -- DONE ----- --------------