Afin de parer au plus pressé et pour permettre la mise à jour des contacts, j'ai du modifié la structure du champ DateModified de la table Abook, voici la requete de modification:
ALTER TABLE `Abook` CHANGE `DateModified` `DateModified` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
Insertion d'un contact
INSERT INTO Abook (UserHomePhone, UserHomeMobile, UserHomeFax, UserWorkPhone, UserWorkMobile, UserWorkFax, UserEmail, UserEmail2, UserEmail3, UserEmail4, UserEmail5, UserFirstName, UserLastName, UserMiddleName, UserHomeAddress, UserHomeCity, UserHomeState, UserHomeZip, UserHomeCountry, UserWorkAddress, UserWorkCity, UserWorkState, UserWorkZip, UserWorkCountry, UserInfo, UserTitle, UserGender, UserDOB, UserURL, UserWorkCompany, UserWorkTitle, UserWorkDept, SIP, Shared, Account, DateAdded, DateModified) VALUES (//, //, //, '09 80 50 50 50', '06 00 00 00 00', //, 'f.laborde@aditu.fr', //, //, //, //, 'Franck', 'Laborde', //, //, //, //, //, //, 'Pavillon d'Izarbel - Terrasses Claude Shannon - Technopole Izarbel Côte Basque -', 'Bidart', //, '64210', 'France', //, //, //, 'DATE de naissance', 'http://www.aditu.fr', 'Aditu', 'Administrateur systèmes & réseaux', 'Exploitation', //, '0', 'b.perrin@hodei.net', NOW(), '41619.866250')
Partage du contact avec tous les utilisateurs du domaine
UPDATE Abook SET UserHomePhone = //, UserHomeMobile = //, UserHomeFax = //, UserWorkPhone = '09 80 50 50 50', UserWorkMobile = '06 00 00 00 00', UserWorkFax = //, UserEmail = 'f.laborde@aditu.fr', UserEmail2 = //, UserEmail3 = //, UserEmail4 = //, UserEmail5 = //, UserFirstName = 'Franck', UserLastName = 'Laborde', UserMiddleName = //, UserHomeAddress = //, UserHomeCity = //, UserHomeState = //, UserHomeZip = //, UserHomeCountry = //, UserWorkAddress = 'Pavillon d''Izarbel - Terrasses Claude Shannon - Technopole Izarbel Côte Basque -', UserWorkCity = 'Bidart', UserWorkState = //, UserWorkZip = '64210', UserWorkCountry = 'France', UserInfo = //, UserTitle = //, UserGender = //, UserDOB = NULL, UserURL = 'http://www.aditu.fr', UserWorkCompany = 'Aditu', UserWorkTitle = 'Administrateur systèmes & réseaux', UserWorkDept = 'Exploitation', SIP = //, Shared = '1', DateModified = '41619.873831' WHERE (id = 7)
DELETE FROM `AbookPermissions` WHERE (AbookID = 7) INSERT INTO `AbookPermissions` (`Account`, `Permissions`, `AbookID`) VALUES ('f.milhau@hodei.net', '1', '7') INSERT INTO `AbookPermissions` (`Account`, `Permissions`, `AbookID`) VALUES ('f.patissier@hodei.net', '1', '7') INSERT INTO `AbookPermissions` (`Account`, `Permissions`, `AbookID`) VALUES ('b.perrin@hodei.net', '1', '7') INSERT INTO `AbookPermissions` (`Account`, `Permissions`, `AbookID`) VALUES ('b.perrin@hodei.net', '1', '7')
Création d'un contact partagé
INSERT INTO erp_socpeople ( datec, fk_soc, lastname, firstname, fk_user_creat, priv, canvas, entity, import_key) VALUES ('20131203233615',NULL,'Perrin','Benjamin', '1', 0, NULL, 1, NULL)
UPDATE erp_socpeople SET fk_soc=NULL, civilite='MR', lastname='Perrin', firstname='Benjamin', address='22 avenue verdun', zip='92300', town='Courbevoie', fk_pays=1, fk_departement=NULL, poste='Admin sys', fax='0122334455', email='b.perrin@hodei.net', note_private = //, note_public = //, phone = '0199887766', phone_perso = '0134970404', phone_mobile = '0624632069', jabberid = //, priv = '0', fk_user_modif='1', default_lang=NULL, no_email=0 WHERE rowid=2
DELIMITER ; DROP TRIGGER erpINSabook; DELIMITER // CREATE TRIGGER erpINSabook AFTER INSERT ON llx_socpeople FOR EACH ROW BEGIN DECLARE Share INT(1); DECLARE LoginErp VARCHAR(255); DECLARE Owner VARCHAR(255); IF(NEW.priv=1)THEN SET Share = 0; ELSE SET LoginErp = (SELECT login FROM llx_user WHERE rowid = NEW.fk_user_creat); SET Owner = (SELECT Account FROM atmail.Users WHERE Account = LoginErp); SET Share = 1; INSERT INTO atmail.Abook (UserLastName,UserFirstName, Shared, Account, DateAdded, DateModified) VALUES (NEW.lastname, NEW.firstname, Share, Owner, NOW(), NEW.rowid); END IF; END// DELIMITER ;
DELIMITER ; DROP TRIGGER before_update_socpeople; DELIMITER // CREATE TRIGGER before_update_socpeople BEFORE UPDATE ON llx_socpeople FOR EACH ROW BEGIN DECLARE LoginErp VARCHAR(255); DECLARE Owner VARCHAR(255); DECLARE DOMAIN VARCHAR(255); DECLARE DefAccount VARCHAR(255); DECLARE UidGen VARCHAR(50); DECLARE IdContact INT; SET LoginErp = (SELECT login FROM llx_user WHERE rowid = NEW.fk_user_modif); SET Owner = (SELECT Account FROM atmail.Users WHERE Account = LoginErp); SET DOMAIN = (SELECT SUBSTRING_INDEX(Owner, '@', -1)); SET DefAccount = CONCAT('hodei-erp','@',DOMAIN); SET UidGen = (SELECT MD5(CONCAT(OLD.rowid,(SELECT unix_timestamp(OLD.datec)-3600),DefAccount))); SET IdContact = (SELECT id FROM atmail.Abook WHERE DateModified=UidGen); IF(IdContact IS NULL AND NEW.priv = 0)THEN INSERT INTO atmail.Abook (UserLastName,UserFirstName, Shared, Account, DateAdded, DateModified) VALUES (NEW.lastname, NEW.firstname, '0', Owner, NOW(), UidGen); END IF; END IF; END// DELIMITER ;
DELIMITER ; DROP TRIGGER after_update_socpeople; DELIMITER // CREATE TRIGGER after_update_socpeople AFTER UPDATE ON llx_socpeople FOR EACH ROW BEGIN DECLARE Share INT(1); DECLARE LoginErp VARCHAR(255); DECLARE Owner VARCHAR(255); DECLARE DOMAIN VARCHAR(255); DECLARE done INT DEFAULT 0; DECLARE Country VARCHAR(255); DECLARE Company VARCHAR(255); DECLARE IdContact INT; DECLARE Ac1 VARCHAR(255); DECLARE CheckContact INT; DECLARE DefAccount VARCHAR(255); DECLARE UidGen VARCHAR(50); DECLARE ShareUsers CURSOR FOR SELECT Account FROM atmail.Users WHERE Account LIKE (CONCAT('%@',DOMAIN)); DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; IF(NEW.priv=1)THEN SET Share = 0; ELSE SET LoginErp = (SELECT login FROM llx_user WHERE rowid = NEW.fk_user_modif); SET Owner = (SELECT Account FROM atmail.Users WHERE Account = LoginErp); SET DOMAIN = (SELECT SUBSTRING_INDEX(Owner, '@', -1)); SET DefAccount = CONCAT('hodei-erp','@',DOMAIN); SET UidGen = (SELECT MD5(CONCAT(OLD.rowid,unix_timestamp(OLD.datec)-3600,DefAccount))); SET IdContact = (SELECT id FROM atmail.Abook WHERE DateModified=UidGen); IF(NEW.fk_soc > 0)THEN SET Company = (SELECT nom FROM llx_societe WHERE rowid = NEW.fk_soc); END IF; IF (NEW.fk_pays > 0)THEN SET Country = (SELECT libelle FROM llx_c_pays WHERE rowid = NEW.fk_pays); END IF; UPDATE atmail.Abook SET UserEmail=NEW.email, UserFirstName=NEW.firstname, UserLastName=NEW.lastname, UserTitle=NEW.civilite, UserDOB=NULL, UserHomePhone=NEW.phone_perso, UserWorkCompany=Company, UserWorkTitle=NEW.poste, UserWorkOffice=NULL, UserWorkAddress=NEW.address, UserWorkCity=NEW.town, UserWorkZip=NEW.zip, UserWorkCountry=Country, UserWorkPhone=NEW.phone, UserWorkMobile=NEW.phone_mobile, UserWorkFax=NEW.fax, UserType=NULL,Shared='1', Account=DefAccount WHERE DateModified = UidGen; DELETE FROM atmail.AbookPermissions WHERE AbookID = IdContact; OPEN ShareUsers; REPEAT FETCH ShareUsers INTO Ac1; INSERT INTO atmail.AbookPermissions (AbookID, Account, Permissions) VALUES(IdContact, Ac1, '1' ); UNTIL done END REPEAT; CLOSE ShareUsers; END IF; END// DELIMITER ;
Piste: