-- SQLtweakFORkevync.sql
-- 2023-09-17 Tom Holden ve3meo
-- in response to https://sqlitetoolsforrootsmagic.com/forum/topic/tweak-for-sqlite-statment/

-- PlaceCommaParse.sql
/*
2013-02-17 Tom Holden ve3meo
2023-09-17 rev to operate on Name, not Standarized field

Creates a temporary table of non-empty Place names with the
positions of up to three commas in the string. Can be used to parse out
the 4 parts of the name for further use such as the generation of a 2-part 
Abbreviation and 3-part Name for reports. 
*/
DROP TABLE IF EXISTS xPlaceCommaTable;
	
CREATE TEMP TABLE xPlaceCommaTable AS
SELECT PlaceID
	,Name
	,Comma1
	,Comma2
	,Comma2 + INSTR(SUBSTR(Name, Comma2 + 1), ',') AS Comma3
FROM (
	SELECT PlaceID
		,Name
		,Comma1
		,Comma1 + INSTR(SUBSTR(Name, Comma1 + 1), ',') AS Comma2
	FROM (
		SELECT PlaceID
			,Name
			,INSTR(Name, ',') AS Comma1
		FROM PlaceTable
		WHERE PlaceType = 0
			AND Name NOT LIKE ''
		)
	);
 
 -- PlaceParse.sql
/*
   2013-02-17 Tom Holden ve3meo
   2023-09-17 rev to operate on Name, not Standarized field
 
   Requires existence of table created by PlaceCommaParse.sql.
   Extracts the parts of a 4-part Place name and saves them
   to a temporary table
   */
DROP TABLE IF EXISTS xPlacePartsTable;
 
CREATE TEMP TABLE xPlacePartsTable AS
SELECT *
    ,CASE
        WHEN Comma1 > 0
            THEN SUBSTR(Name, 1, Comma1 - 1)
        ELSE Name
        END AS Place1
    ,CASE
        WHEN Comma2 > Comma1
            THEN SUBSTR(Name, Comma1 + 1, Comma2 - Comma1 - 1)
        WHEN Comma1 > 0
            THEN SUBSTR(Name, Comma1 + 1)
        ELSE ''
        END AS Place2
    ,CASE
        WHEN Comma3 > Comma2
            THEN SUBSTR(Name, Comma2 + 1, Comma3 - Comma2 - 1)
        WHEN Comma2 > Comma1
            THEN SUBSTR(Name, Comma2 + 1)
        ELSE ''
        END AS Place3
    ,CASE
        WHEN Comma3 > Comma2
            THEN SUBSTR(Name, Comma3 + 1)
        ELSE ''
        END AS Place4
FROM xPlaceCommaTable;

-- kevync's script tweaked
SELECT

null as a1,
'[' || FT.Name || '] ' || pt.Reverse || ' ' || (substr(E.Date,4,4)) as b2, -- name
'[' || FT.Name || '] ' ||
(substr(E.Date,4,4)) || ' ' ||
'[' || ML.LinkID || '] ' ||
'[' || ML.MediaID || '] ' ||
'[' || nt.OwnerID || '] ' ||
'[' || pt.PlaceID || '] ' ||
'[' || E.EventID || '] ' as c3, -- RefNumber
'[' || FT.Name || '] ' || (substr(E.Date,4,4)) || ' ' || pt.Reverse as d4, -- actual text
'Image' as e5, -- comments
null as f6, -- IsPrivate
10003 as g7, -- TemplateID

'<Root><Fields>
<Field><Name>Date</Name><Value>' || substr(E.Date,4,4) ||'</Value></Field>
<Field><Name>Country</Name><Value>' || substr(pt.Reverse, 0, instr(pt.Reverse,', ')+0) ||'</Value></Field>
<Field><Name>State</Name><Value>' ||
ifnull(xP.Place2,'null')
||'</Value></Field>
<Field><Name>City</Name><Value>' || substr(pt.Name, 1, instr(pt.Name,', ')-1) ||'</Value></Field>
<Field><Name>Place</Name><Value>' || pt.Reverse ||'</Value></Field>
</Fields></Root>' as h8, -- fields
julianday('now') - 2415018.5 as i9 -- UTCmodDate

FROM FactTypeTable AS FT
JOIN EventTable AS E ON E.EventType = 4 AND E.OwnerTYPE = 0 AND E.EventType = FT.FactTypeID
LEFT JOIN MediaLinkTable AS ML ON ML.OwnerType = 2 AND ML.OwnerID = E.EventID
LEFT JOIN NameTable as nt ON (nt.OwnerID = E.OwnerID)
LEFT JOIN PlaceTable as pt ON (e.placeID = pt.PlaceID)
JOIN xPlacePartsTable as xP ON pt.PlaceID = xP.PlaceID
WHERE ML.LinkID IS NOT NULL and nt.IsPrimary and FT.Name is NOT NULL --and Details is NOT NULL 
--AND E.OwnerID = 1643
GROUP BY'[' || FT.Name || '] ' || ' ' || (substr(E.Date,4,4)) || ' ' || pt.Reverse
ORDER BY pt.Reverse, (substr(E.Date,4,4))
;