CREATE DEFINER=`owncloud`@`localhost` TRIGGER `ContactsNameFixUpdatePref` BEFORE UPDATE ON `oc_contacts_cards_properties` FOR EACH ROW BEGIN
SET @fullname := (SELECT fullname FROM `oc_contacts_cards` WHERE id=NEW.contactid );
SET @locspace = locate(' ',@fullname);
IF @locspace>=1 THEN
SET @ln := mid(@fullname,0,@locspace);
SET @fn := mid(@fullname,@locspace,len(@fullname)-@locspace);
ELSE
SET @ln := @fullname;
SET @fn := '';
END IF;
IF NEW.name = 'FN' THEN
IF @fullname <> NEW.value THEN
SET NEW.value = @fullname;
END IF;
ELSEIF NEW.name = 'N' THEN
IF locate(NEW.value,@ln)=0 THEN
SET NEW.value := concat(@ln,';',@fn,';;;');
END IF;
END IF;
END


CREATE DEFINER=`owncloud`@`localhost` TRIGGER `ContactsNameFixInsertPref` BEFORE INSERT ON `oc_contacts_cards_properties` FOR EACH ROW BEGIN
SET @fullname := (SELECT fullname FROM `oc_contacts_cards` WHERE id=NEW.contactid );
SET @locspace = locate(' ',@fullname);
IF @locspace>=1 THEN
SET @ln := mid(@fullname,0,@locspace);
SET @fn := mid(@fullname,@locspace,len(@fullname)-@locspace);
ELSE
SET @ln := @fullname;
SET @fn := '';
END IF;
IF NEW.name = 'FN' THEN
IF @fullname <> NEW.value THEN
SET NEW.value = @fullname;
END IF;
ELSEIF NEW.name = 'N' THEN
IF locate(NEW.value,@ln)=0 THEN
SET NEW.value := concat(@ln,';',@fn,';;;');
END IF;
END IF;
END


CREATE DEFINER=`owncloud`@`localhost` TRIGGER `ContactNameFixUpdate` BEFORE UPDATE ON `oc_contacts_cards` FOR EACH ROW BEGIN
SET @data := NEW.carddata;

SET @firstname := mid(NEW.carddata, locate('X-PHONETIC-FIRST-NAME:',NEW.carddata)+22, locate(0x0d0a,NEW.carddata,locate('X-PHONETIC-FIRST-NAME:',NEW.carddata)+22)-locate('X-PHONETIC-FIRST-NAME:',NEW.carddata)-22);

SET @lastname := mid(
NEW.carddata, locate('X-PHONETIC-LAST-NAME:',NEW.carddata)+21, locate(
0x0d0a,NEW.carddata,locate('X-PHONETIC-LAST-NAME:',NEW.carddata)+21
)-locate('X-PHONETIC-LAST-NAME:',NEW.carddata)-21);
SET @fullnamecard := mid(
NEW.carddata, locate('FN:',NEW.carddata)+3, locate(
0x0d0a,NEW.carddata,locate('FN:',NEW.carddata)+3
)-locate('FN:',NEW.carddata)-3);

IF @fullnamecard = NEW.fullname THEN
SET NEW.fullname := concat(@lastname,0x20,@firstname);
SET @locn := locate('
N:',NEW.carddata);
SET @n := mid(NEW.carddata, @locn, locate(0x0d0a,NEW.carddata,@locn+2)-@locn);
SET NEW.carddata := replace(NEW.carddata,@n,concat('
N:',@firstname,';',@lastname,';;;'));

SET @locfn := locate('FN:',NEW.carddata);
SET @fn := mid(NEW.carddata, @locfn, locate(0x0d0a,NEW.carddata,@locfn)-@locfn);
SET NEW.carddata := replace(NEW.carddata,@fn,concat('FN:',@lastname,0x20,@firstname));

END IF;
SET NEW.lastmodified=NEW.lastmodified+1;
END


CREATE DEFINER=`owncloud`@`localhost` TRIGGER `ContactNameFixInsert` BEFORE INSERT ON `oc_contacts_cards` FOR EACH ROW BEGIN
SET @data := NEW.carddata;

SET @firstname := mid(NEW.carddata, locate('X-PHONETIC-FIRST-NAME:',NEW.carddata)+22, locate(0x0d0a,NEW.carddata,locate('X-PHONETIC-FIRST-NAME:',NEW.carddata)+22)-locate('X-PHONETIC-FIRST-NAME:',NEW.carddata)-22);

SET @lastname := mid(
NEW.carddata, locate('X-PHONETIC-LAST-NAME:',NEW.carddata)+21, locate(
0x0d0a,NEW.carddata,locate('X-PHONETIC-LAST-NAME:',NEW.carddata)+21
)-locate('X-PHONETIC-LAST-NAME:',NEW.carddata)-21);
SET @fullnamecard := mid(
NEW.carddata, locate('FN:',NEW.carddata)+3, locate(
0x0d0a,NEW.carddata,locate('FN:',NEW.carddata)+3
)-locate('FN:',NEW.carddata)-3);

IF @fullnamecard = NEW.fullname THEN
SET NEW.fullname := concat(@lastname,0x20,@firstname);
SET @locn := locate('
N:',NEW.carddata);
SET @n := mid(NEW.carddata, @locn, locate(0x0d0a,NEW.carddata,@locn+2)-@locn);
SET NEW.carddata := replace(NEW.carddata,@n,concat('
N:',@firstname,';',@lastname,';;;'));

SET @locfn := locate('FN:',NEW.carddata);
SET @fn := mid(NEW.carddata, @locfn, locate(0x0d0a,NEW.carddata,@locfn)-@locfn);
SET NEW.carddata := replace(NEW.carddata,@fn,concat('FN:',@lastname,0x20,@firstname));

END IF;
SET NEW.lastmodified=NEW.lastmodified+1;
END