--Reports-NewParagraphGeneralNote.sql
/* 2016-08-09 Tom Holden ve3meo
rev2016-08-12 Was not operating on Note with no leading CR/LF

Strips up to two leading CR/LFs if any from the General Note and then
prepends two leading CR/LFs to all non-empty General notes in order to
force a new paragraph in the narrative reports.
*/

DROP VIEW IF EXISTS vBareNote
;
CREATE TEMP VIEW vBareNote AS
SELECT 
  PersonID
  , CASE
      WHEN SUBSTR(Note,1,2) LIKE CAST (X'0D0A' AS TEXT)||'%'
      THEN REPLACE(SUBSTR(Note,1,4),CAST (X'0D0A' AS TEXT),'') || SUBSTR(Note, 5)
      ELSE Note
    END AS BareNote
FROM PersonTable WHERE LENGTH(Note)>0  
;  

DROP VIEW IF EXISTS vNewNote
;
CREATE TEMP VIEW vNewNote AS
SELECT 
  PersonID
  , CAST (X'0D0A0D0A' AS TEXT) || BareNote AS NewNote
FROM vBareNote
;

BEGIN
;
UPDATE PersonTable
SET Note = 
  CAST(
       (SELECT NewNote FROM vNewNote 
        WHERE PersonTable.PersonID = vNewNote.PersonID
        ) 
       AS BLOB
       )
WHERE PersonTable.PersonID IN (SELECT PersonID FROM vNewNote)
;
COMMIT
;