====== Dolibarr ======

Définition de la table actioncomm: [[http://wiki.dolibarr.org/index.php/Table_llx_actioncomm|http://wiki.dolibarr.org/index.php/Table_llx_actioncomm]]

===== Insertion d'un rendez-vous / événement unique =====

<code sql>
INSERT INTO llx_actioncomm
(datec,datep,datep2,durationp,fk_action,code,fk_soc,fk_project,note,fk_contact,fk_user_author,fk_user_action,fk_user_done,label,percent,priority,fulldayevent,location,punctual,transparency,fk_element,elementtype,entity)
VALUES ('20131213105345','20131213121500','20131213131500','3600',50,NULL,NULL,NULL, //,NULL,'1','1',NULL,'test d'agenda','-1','0','0',//,'1','0',null,null,1)
</code>

===== Rendez-vous / événement sur une journée =====

La différence intervient sur le champ booléen 'fulldayevent'

<code sql>
INSERT INTO llx_actioncomm
(datec,datep,datep2,durationp,fk_action,code,fk_soc,fk_project,note,fk_contact,fk_user_author,fk_user_action,fk_user_done,label,percent,priority,fulldayevent,location,punctual,transparency,fk_element,elementtype,entity)
VALUES ('20131217222748','20131227000000','20131227235959','86399',50,NULL,NULL,NULL, 'Gros event de ouf !!! Soirée Trigger Sexy',NULL,'2','2',NULL,'Salon de la synchronisation','-1','0','1','Sur mon bureau','1','0',NULL,NULL,1)
</code>

===== Modification d'un rendez-vous =====

La distinction entre un événement 'unique' et une journée complète est toujours lié au champ 'fulldayevent'

<code sql>
UPDATE llx_actioncomm  SET percent = '-1', label = 'le 26 on mange du SQL en force', datep = '20131226000000', datep2 = NULL, note = 'Et c''est toute la journée', fk_soc =NULL, fk_project =NULL, fk_contact =NULL, priority = '0', fulldayevent = '1', location = 'Ou tu veux', transparency = '0', fk_user_mod = '2', fk_user_action='2', fk_user_done=NULL
WHERE id=1
</code>

====== ATmail ======

ATmail repose sur le standard iCalendar: [[http://fr.wikipedia.org/wiki/ICalendar|http://fr.wikipedia.org/wiki/ICalendar]]

===== Insertion d'un rendez-vous / événement unique =====

<code sql>
INSERT INTO `calendarObjects`
(calendarid, uri, calendardata, lastmodified, START, END, rrule) VALUES ('2','3cedcfbd-925b-4786-a81f-f401942e859f.ics','BEGIN:VCALENDARrnVERSION:2.0rnPRODID:-//Atmail//6.0//ENrnCALSCALE:GREGORIANrnBEGIN:VEVENTrnSEQUENCE:1rnTRANS:OPAQUErnUID:3cedcfbd-925b-4786-a81f-f401942e859frnDTSTART;VALUE=date:20140113rnDTEND:20140114rnSUMMARY:Nouvel événementrnDESCRIPTION:rnDTSTAMP:20131213T134312ZrnEND:VEVENTrnEND:VCALENDARrn','1386942192','1389571200','1389657600','0')

UPDATE `calendars` SET ctag = ctag + 1 WHERE id = '2'

UPDATE `calendarObjects` SET calendardata = 'BEGIN:VCALENDARrnVERSION:2.0rnPRODID:-//Atmail//6.0//ENrnCALSCALE:GREGORIANrnBEGIN:VEVENTrnSEQUENCE:2rnTRANS:OPAQUErnUID:3cedcfbd-925b-4786-a81f-f401942e859frnDTSTART:20140113T090000rnDTEND:20140113T100000rnSUMMARY:CopilrnDESCRIPTION:rnDTSTAMP:20131213T134554ZrnLAST-MODIFIED:20131213T134554ZrnLOCATION:test du deuxieme champ librernCATEGORY:BusinessrnAVAILABILITY:BusyrnBEGIN:VALARMrnTRIGGER:-PT15HrnDESCRIPTION:Alarm for: "Copil"rnACTION:DISPLAYrnEND:VALARMrnRRULE:FREQ=DAILY;INTERVAL=1rnRRULE2:FREQ=DAILY;COUNT=400rnEND:VEVENTrnEND:VCALENDARrn', lastmodified = '1386942354', START = '1389603600', END = '1389607200', rrule = '1'
WHERE calendarid = '2' AND uri = '3cedcfbd-925b-4786-a81f-f401942e859f.ics'
</code>

<code sql>
UPDATE calendars SET ctag = ctag + 1 WHERE id = '2'
</code>

===== Rendez-vous / événement sur une journée =====

La différence intervient dans le champ calendardata sur les propriétés DTSTART et DTEND.

<code sql>
UPDATE `calendarObjects` SET calendardata = 'BEGIN:VCALENDARrnVERSION:2.0rnPRODID:-//Atmail//6.0//ENrnCALSCALE:GREGORIANrnBEGIN:VEVENTrnSEQUENCE:2rnTRANS:OPAQUErnUID:6626c386-2507-4669-a85f-fb75cc13e1ecrnDTSTART;VALUE=DATE:20131217rnDTEND;VALUE=DATE:20131218rnSUMMARY:journee fulrnDESCRIPTION:rnDTSTAMP:20131217T210820ZrnLAST-MODIFIED:20131217T210820ZrnLOCATION:chez toirnCATEGORY:GeneralrnAVAILABILITY:BusyrnEND:VEVENTrnEND:VCALENDARrn', lastmodified = '1387314501', START = '1387238400', END = '1387324800', rrule = '0'
WHERE calendarid = '10' AND uri = '6626c386-2507-4669-a85f-fb75cc13e1ec.ics'
</code>

===== Modification d'un rendez-vous =====

Pour le suivi des modification, le paramètre "SEQUENCE" dans le champ calendardata doit être incrémenté de +1.

<code sql>
UPDATE calendarObjects
SET calendardata = 'BEGIN:VCALENDARrnVERSION:2.0rnPRODID:-//Atmail//6.0//ENrnCALSCALE:GREGORIANrnBEGIN:VEVENTrnSEQUENCE:2rnTRANS:OPAQUErnUID:eaa361e1-70dc-cc21-1c88-fcf20226332d3ernDTSTART;VALUE=DATE:20131226rnDTEND;VALUE=DATE:20131226rnSUMMARY:le 26 on mange du SQLrnDESCRIPTION:Et c''est toute la journéernDTSTAMP:20131218T151600ZrnLAST-MODIFIED:20131218T151600ZrnLOCATION:Ou tu veuxrnCATEGORY:BusinessrnAVAILABILITY:BusyrnORGANIZER;CN=bperrin@nimbo.lan:MAILTO:bperrin@nimbo.lanrnEND:VEVENTrnEND:VCALENDARrn',
lastmodified = '1387379761',
START = '1388016000',
END = '1388016000',
rrule = '0'
WHERE calendarid = '10' AND uri = 'eaa361e1-70dc-cc21-1c88-fcf20226332d3e.ics'
</code>

<code sql>
UPDATE calendars SET ctag = ctag + 1 WHERE id = '10'
</code>

====== Synchronisation Dolibarr/Agenda vers Atmail ======

  * Les dates Dolibarr sont formatées avec des '-' et des ':', celle de Atmail sans, il faut donc les supprimés, j'ai créé la fonction split_string pour effectuer les découpes et la fonction dateERP2ATMAIL pour concaténé les découpes et géré la valeur retourné en function de l'événement (journée complète ou non).
  * La création d'un événement iCal est intégré dans un champ unique nommé calendardata, ce champ à toutes les informations sur l'événement.
  * Si le champ "elementtype" de dolibarr est différent de Null on ne créé pas d'événement dans Atmail car il correspond à un événement système dans Dolibarr
  * *Il est nécessaire de créer un identifiant unique pour un événément, pour ce faire, j'ai créé la formule suivante: MD5( CONCAT( NEW.id, [[:systeme_informatique:atmail:timestampcreateutc|TimeStampCreateUTC]], [[:systeme_informatique:atmail:owneratmail|OwnerAtmail]],(SELECT RAND())));
      * On concatène l'id de l'événement dolibarr avec le timestamp de création (dolibarr), le nom du proprietaire de l'événement atmail et un nombre alétoire, tout cela hashé par un MD5.
      * Les entrées DTSTART, DTEND, LAST-MODIFIED, DTSTAMP demande un format particulier
      * La category est fixé sur Business
      * Si dans dolibarr ont spécifie que cette événement est associé à un utilisateur autre que le créateur, alors on créé l'événement dans l'agenda de l'utilisateur en question
  * On récupère l'id du calendrier 'private' pour mettre à jour le compteur ctag
  * Utilisation du champ PRODID pour stocker l'id dolibarr

===== Trigger MYSQL après un INSERT calendar dans Dolibarr: after_insert_actioncomm =====

<code sql>
 DELIMITER ;
  DROP FUNCTION IF EXISTS split_string;
  DROP FUNCTION IF EXISTS dateERP2ATMAIL;
  DROP TRIGGER after_insert_actioncomm;
 DELIMITER //
 CREATE FUNCTION split_string (stringToSplit VARCHAR(256), sign VARCHAR(12), POSITION INT(2)) RETURNS VARCHAR(255)
  BEGIN
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(stringToSplit,sign,POSITION),LENGTH(SUBSTRING_INDEX(stringToSplit, sign, POSITION -1)) + 1), sign, //);
  END//
 CREATE FUNCTION dateERP2ATMAIL(DATE VARCHAR(255),Format VARCHAR(5)) RETURNS VARCHAR(255)
    BEGIN
      DECLARE YEAR CHAR(4);
      DECLARE MONTH CHAR(2);
      DECLARE DAY CHAR(2);
      DECLARE HOUR CHAR(2);
      DECLARE MINUTE CHAR(2);
      DECLARE Seconde CHAR(2);
      DECLARE DateSplit CHAR(10);
      DECLARE TimeSplit CHAR(8);

      SET DateSplit = (SELECT split_string(DATE,' ', 1) AS name);
      SET TimeSplit = (SELECT split_string(DATE,' ', 2) AS name);
      SET YEAR = (SELECT split_string(DateSplit,'-', 1) AS name);
      SET MONTH = (SELECT split_string(DateSplit,'-', 2) AS name);
      SET DAY = (SELECT split_string(DateSplit,'-', 3) AS name);
      SET HOUR = (SELECT split_string(TimeSplit,':', 1) AS name);
      SET MINUTE = (SELECT split_string(TimeSplit,':', 2) AS name);
      SET Seconde = (SELECT split_string(TimeSplit,':', 3) AS name);
      IF(Format = "all") THEN
        RETURN CONCAT(YEAR,MONTH,DAY,'T',HOUR,MINUTE,Seconde);
      ELSE
        RETURN CONCAT(YEAR,MONTH,DAY);
      END IF;
    END//
 DELIMITER //
 CREATE TRIGGER after_insert_actioncomm AFTER INSERT ON llx_actioncomm
  FOR EACH ROW BEGIN
    DECLARE Owner VARCHAR(255);
    DECLARE OwnerAtmail VARCHAR(255);
    DECLARE StartDate VARCHAR(30);
    DECLARE EndDate VARCHAR(30);
    DECLARE TimeStampStart VARCHAR(30);
    DECLARE TimeStampEnd VARCHAR(30);
    DECLARE TimeStampCreateUTC VARCHAR(30);
    DECLARE DtStampCreate VARCHAR(30);
    DECLARE TimeStampLastModifUTC VARCHAR(50);
    DECLARE LastModifiedUTC VARCHAR(50);
    DECLARE UidBase VARCHAR(50);
    DECLARE UidOk VARCHAR(50);
    DECLARE UriIcsFile VARCHAR(50);
    DECLARE IcsData MEDIUMTEXT;
    DECLARE IdCalendar INT(11);
    DECLARE FormatDate VARCHAR(10);
    DECLARE StringDate VARCHAR(20);
  IF ( NEW.elementtype = NULL ) THEN
      SET Owner = (SELECT login FROM llx_user WHERE rowid = NEW.fk_user_action);
      SET OwnerAtmail = (SELECT Account FROM atmail.Users WHERE Account = Owner);
    IF( NEW.fulldayevent=1 )THEN
      SET FormatDate = 'day';
      SET TimeStampEnd = (SELECT unix_timestamp(NEW.datep)+86399);
      SET StringDate = (SELECT ";VALUE=DATE:");
    ELSE
      SET FormatDate = 'all';
      SET TimeStampEnd = (SELECT unix_timestamp(NEW.datep2));
      SET StringDate = (SELECT ":");
    END IF;
      SET StartDate = CONCAT(StringDate,(SELECT dateERP2ATMAIL(NEW.datep,FormatDate)));
      SET EndDate = CONCAT(StringDate,(SELECT dateERP2ATMAIL(NEW.datep,FormatDate)));
      SET TimeStampStart = (SELECT unix_timestamp(NEW.datep));
      SET TimeStampCreateUTC = (SELECT unix_timestamp(NEW.datec)-3600);
      SET DtStampCreate = CONCAT((SELECT FROM_UNIXTIME(TimeStampCreateUTC,'%Y%m%d')),'T',(SELECT FROM_UNIXTIME(TimeStampCreateUTC,'%H%i%s')),'Z');
      SET TimeStampLastModifUTC = (SELECT unix_timestamp(NEW.tms)-3600);
      SET LastModifiedUTC = CONCAT((SELECT FROM_UNIXTIME(TimeStampLastModifUTC,'%Y%m%d')),'T',(SELECT FROM_UNIXTIME(TimeStampLastModifUTC,'%H%i%s')),'Z');
      SET UidBase = MD5(CONCAT(NEW.id,TimeStampCreateUTC,OwnerAtmail,(SELECT RAND())));
      SET UidOk = concat(SUBSTRING(UidBase,1,8),'-',SUBSTRING(UidBase,9,4),'-',SUBSTRING(UidBase,12,4),'-',SUBSTRING(UidBase,15,4),'-',SUBSTRING(UidBase,19));
      SET UriIcsFile = CONCAT(UidOK,'.ics');
      SET IcsData = CONCAT("BEGIN:VCALENDARrn","VERSION:2.0rn","PRODID:-//Atmail//6.0//ENrn","CALSCALE:GREGORIANrn","BEGIN:VEVENTrn","SEQUENCE:1rn","TRANS:OPAQUErn",
 "UID:",UidOk,"rn","DTSTART",StartDate,"rn","DTEND",EndDate,"rn","SUMMARY:",NEW.label,"rn","DESCRIPTION:",NEW.note,"rn","DTSTAMP:",DtStampCreate,"rn","LAST-MODIFIED:",
 LastModifiedUTC,"rn","LOCATION:",NEW.location,"rn","CATEGORY:Businessrn","AVAILABILITY:Busyrn","ORGANIZER;CN=",OwnerAtmail,":MAILTO:",OwnerAtmail,"rn","END:VEVENTrn","END:VCALENDARrn");
      SET IdCalendar = (SELECT id FROM atmail.calendars WHERE principaluri = CONCAT('principals/users/',OwnerAtmail) AND displayname = 'Private');
      INSERT INTO atmail.calendarObjects (calendardata,uri,calendarid,lastmodified,START,END,rrule)VALUES(IcsData,UriIcsFile,IdCalendar,TimeStampLastModifUTC,TimeStampStart,TimeStampEnd,'0');
      UPDATE atmail.calendars SET ctag = ctag + 1 WHERE id = IdCalendar;
  END IF;
 END//
 DELIMITER ;
</code>

===== Trigger MYSQL après un UPDATE calendar dans Dolibarr: after_update_actioncomm =====

<code sql>
 DROP TRIGGER IF EXISTS after_update_actioncomm;
 DELIMITER //
 CREATE TRIGGER after_update_actioncomm AFTER UPDATE ON llx_actioncomm
 FOR EACH ROW BEGIN
   DECLARE Owner VARCHAR(255);
   DECLARE OwnerAtmail VARCHAR(255);
   DECLARE StartDate VARCHAR(30);
   DECLARE EndDate VARCHAR(30);
   DECLARE TimeStampStart VARCHAR(30);
   DECLARE TimeStampEnd VARCHAR(30);
   DECLARE TimeStampCreateUTC VARCHAR(30);
   DECLARE DtStampCreate VARCHAR(30);
   DECLARE CalendarObjectsId INT(11);
   DECLARE TimeStampLastModifUTC VARCHAR(50);
   DECLARE LastModifiedUTC VARCHAR(50);
   DECLARE UidBase VARCHAR(50);
   DECLARE UidOk VARCHAR(50);
   DECLARE UriIcsFile VARCHAR(50);
   DECLARE IcsData MEDIUMTEXT;
   DECLARE IdCalendar INT(11);
   DECLARE FormatDate VARCHAR(10);
   DECLARE StringDate VARCHAR(20);
   DECLARE DolActioncommId INT(11);
   DECLARE SEQUENCE INT(11);
   SET Owner = (SELECT login FROM llx_user WHERE rowid = OLD.fk_user_action);
   SET OwnerAtmail = (SELECT Account FROM atmail.Users WHERE Account = Owner);
   IF( NEW.fulldayevent=1 )THEN
     SET FormatDate = 'day';
     SET TimeStampEnd = (SELECT unix_timestamp(NEW.datep)+86399);
     SET StringDate = (SELECT ";VALUE=DATE:");
   ELSE
     SET FormatDate = 'all';
     SET TimeStampEnd = (SELECT unix_timestamp(NEW.datep2));
     SET StringDate = (SELECT ":");
     IF(TimeStampEnd IS NULL)THEN
       SET TimeStampEnd = (SELECT unix_timestamp(NEW.datep));
     END IF;
   END IF;
   SET StartDate = CONCAT(StringDate,(SELECT dateERP2ATMAIL(NEW.datep,FormatDate)));
   SET EndDate = CONCAT(StringDate,(SELECT dateERP2ATMAIL(NEW.datep,FormatDate)));
   SET TimeStampStart = (SELECT unix_timestamp(NEW.datep));
   SET TimeStampCreateUTC = (SELECT unix_timestamp(OLD.datec)-3600);
   SET DtStampCreate = CONCAT((SELECT FROM_UNIXTIME(TimeStampCreateUTC,'%Y%m%d')),'T',(SELECT FROM_UNIXTIME(TimeStampCreateUTC,'%H%i%s')),'Z');
   SET TimeStampLastModifUTC = (SELECT unix_timestamp(NEW.tms)-3600);
   SET LastModifiedUTC = CONCAT((SELECT FROM_UNIXTIME(TimeStampLastModifUTC,'%Y%m%d')),'T',(SELECT FROM_UNIXTIME(TimeStampLastModifUTC,'%H%i%s')),'Z');
   SET DolActioncommId = OLD.id;
   SET IdCalendar = (SELECT id FROM atmail.calendars WHERE principaluri = CONCAT('principals/users/',OwnerAtmail) AND displayname = 'Private');
   SET CalendarObjectsId = (SELECT calendarObjects.id FROM atmail.calendars LEFT JOIN atmail.calendarObjects ON calendars.id = calendarObjects.calendarid WHERE principaluri = CONCAT('principals/users/', OwnerAtmail)
   AND calendardata LIKE CONCAT('%DOLID#',OLD.id,'#%'));
   IF(CalendarObjectsId IS NULL)THEN
     SET UidBase = MD5(CONCAT(NEW.id,TimeStampCreateUTC,OwnerAtmail,(SELECT RAND())));
     SET UidOk = concat(SUBSTRING(UidBase,1,8),'-',SUBSTRING(UidBase,9,4),'-',SUBSTRING(UidBase,12,4),'-',SUBSTRING(UidBase,15,4),'-',SUBSTRING(UidBase,19));
     SET UriIcsFile = CONCAT(UidOK,'.ics');
     SET IcsData = CONCAT("BEGIN:VCALENDAR","VERSION:2.0","PRODID:-//Atmail//6.0//EN//DOLID#",DolActioncommId,"#","","CALSCALE:GREGORIAN","BEGIN:VEVENT","SEQUENCE:1","TRANS:OPAQUE","UID:",UidOk,"","DTSTART",StartDate,"","DTEND",EndDate,"","SUMMARY:",NEW.label,"","DESCRIPTION:",NEW.note,"","DTSTAMP:",DtStampCreate,"","LAST-MODIFIED:", LastModifiedUTC,"","LOCATION:",NEW.location,"","CATEGORY:Business","AVAILABILITY:Busy","ORGANIZER;CN=",OwnerAtmail,":MAILTO:",OwnerAtmail,"","END:VEVENT","END:VCALENDAR");

     INSERT INTO atmail.calendarObjects (calendardata,uri,calendarid,lastmodified,START,END,rrule)VALUES(IcsData,UriIcsFile,IdCalendar,TimeStampLastModifUTC,TimeStampStart,TimeStampEnd,'0');
     UPDATE atmail.calendars SET ctag = ctag + 1 WHERE id = IdCalendar;
   ELSE
     SET IcsData = (SELECT calendardata FROM atmail.calendarObjects WHERE id = CalendarObjectsId);
     SET SEQUENCE = (SELECT (SUBSTRING(IcsData, INSTR(IcsData,"SEQUENCE:")+9, INSTR(IcsData,"TRANS:")-(INSTR(IcsData,"SEQUENCE:")+9)))+1);
     SET UidOk = (SELECT LEFT((SELECT uri FROM atmail.calendarObjects WHERE id = CalendarObjectsId ), 38));
     SET UriIcsFile = CONCAT(UidOK,'.ics');
     SET IcsData = CONCAT("BEGIN:VCALENDAR","VERSION:2.0","PRODID:-//Atmail//6.0//EN//DOLID#",DolActioncommId,"#","","CALSCALE:GREGORIAN","BEGIN:VEVENT","SEQUENCE:",SEQUENCE,"","TRANS:OPAQUE","UID:",UidOk,"","DTSTART",StartDate,"","DTEND",EndDate,"","SUMMARY:",NEW.label,"","DESCRIPTION:",NEW.note,"","DTSTAMP:",DtStampCreate,"","LAST-MODIFIED:", LastModifiedUTC,"","LOCATION:",NEW.location,"","CATEGORY:Business","AVAILABILITY:Busy","ORGANIZER;CN=",OwnerAtmail,":MAILTO:",OwnerAtmail,"","END:VEVENT","END:VCALENDAR");

     UPDATE atmail.calendarObjects SET calendardata = IcsData, lastmodified=TimeStampLastModifUTC, START=TimeStampStart, END=TimeStampEnd, rrule='0' WHERE calendarid=IdCalendar AND uri=UriIcsFile;
     UPDATE atmail.calendars SET ctag = ctag + 1 WHERE id = IdCalendar;
   END IF;
 END //
 DELIMITER ;
</code>

====== Autre solution ======

Projet de synchronisation de l'agenda dolibarr et atmail dans les deux sens : \\  \\ L'idée est de considérer dolibarr comme un client mobile ‘iphone’ et atmail comme le serveur : \\  \\ • toutes les modifications de l’agenda atmail seront importées dans dolibarr, par exemple quand on cliquera sur l’icône Agenda de dolibarr : il … \\ • les modifications faîtes dans l’agenda dolibarr seront automatiquement envoyées sur atmail \\  \\ Remarque : dans la configuration dolibarr Configuration > Agenda > Import Calendrier Externe on peut spécifier une url pointant sur un fichier ICS (cela peut être votre agenda Gmail ; dans Gmail aller sur Agenda > Partager > Detail du partager > URL ICAL) : cela permet déjà de voir comme fonctionne la synchro dolibarr / Gmail/ \\  \\ dolibarr utilise à priori le fichier comm/action/ical.class.php ([[http://www.on-tech.gr/crm/dolibarr/build/html/d2/d56/ical_8class_8php_source.html|http://www.on-tech.gr/crm/dolibarr/build/html/d2/d56/ical_8class_8php_source.html]]) qui manipuler du ics. \\
 \\
 \\
Pour manipuler le caldav dans dolibarr on s'authentifiera avec la librairie php [[:systeme_informatique:atmail:sabredav|SabreDAV]] ([[http://en.wikipedia.org/wiki/SabreDAV|http://en.wikipedia.org/wiki/SabreDAV]]) (peut etre trop fonctionnel, mais on peut s'en inspirer). \\
 \\
Les données vcalendar d’atmail se trouve dans le champ calendardata de la table calendarObjects.