-- SortDateEncoderDev.sql
/*
2012-12-31 Tom Holden ve3meo
2012-12-31 1.1 Double-year partial support
2013-01-01 1.2 Quaker date partial support

Calculates SortDates from event dates, based on algorithms adapted from R. Steven Turley
and compares to the RM generated SortDates for errors.
See http://sqlitetoolsforrootsmagic.wikispaces.com/Dates%3E+SortDate+Algorithm#Advanced Date Decoder

Encode SortDate for regular non-Quaker dates
Ds = If no Date then 9223372036854775807
     else ((Y1 + 10000)<<49) + (M1<<45) + (D1<<39) + 
     (If Date2 then (Y2 + 10000)<<20 else 17178820608) + (M2<<16) + (D2<<10) + F
where Ds = SortDate, Y = Year, M = Month, D = Day, F = modifier flag.

*/
-- SELECT (1<<62) -1 + (1<<62) -- = 2^63-1 but avoids overflow
-- SELECT ABS((1<<63)+1)  -- also works
SELECT DATE, SortDate, CalcDate, SortDate - CalcDate AS Error, Details
FROM (
	SELECT DATE, CASE SUBSTR(DATE, 1, 1)
			WHEN 'D'
				THEN --non-Quaker date
					CASE 
						WHEN SUBSTR(DATE, 3, 5) = '+0000'
							AND SUBSTR(DATE, 9, 4) != '0000'
							THEN 16383 << 49 -- a date with no year
						ELSE ((CAST(SUBSTR(DATE, 3, 5) AS INT) + 10000 + (SUBSTR(DATE, 12, 1) = '/')) << 49)
						END + --Y1
					(ABS(SUBSTR(DATE, 8, 2)) << 45) + --M1
					(ABS(SUBSTR(DATE, 10, 2)) << 39) + --D1
					CASE SUBSTR(DATE, 14, 5)
						WHEN '+0000'
							THEN 17178820608 -- x'03 FF F0 00 00' or (2^34 - 2^20)  SELECT (1<<34)-(1<<20) 
						ELSE ((CAST(SUBSTR(DATE, 14, 5) AS INT) + 10000) << 20) --Y2
						END + (ABS(SUBSTR(DATE, 19, 2)) << 16) + --M2  
					(ABS(SUBSTR(DATE, 21, 2)) << 10) + --D2
					CASE SUBSTR(DATE, 2, 1) -- Flag
						WHEN 'B'
							THEN 0 --Before
						WHEN 'Y'
							THEN 3 --By
						WHEN 'T'
							THEN 6 --To
						WHEN 'U'
							THEN 9 --Until
						WHEN '.'
							THEN 12 --On
						WHEN 'R'
							THEN 15 --Between x and y
						WHEN 'S'
							THEN 18 --From x to y
						WHEN '-'
							THEN 21 --Hyphen x-y
						WHEN 'O'
							THEN 24 --x Or y
						WHEN 'F'
							THEN 1047579 --From    (27 + x'FFC00')
						WHEN 'I'
							THEN 1047582 --Since   (30 + x'FFC00')
						WHEN 'A'
							THEN 1047583 --After   (31 + x'FFC00')
						ELSE 0
						END
			WHEN 'Q'
				THEN --Quaker
					CASE 
						WHEN ABS(SUBSTR(DATE, 8, 2)) > 10
							AND ABS(SUBSTR(DATE, 3, 5)) < 1752 -- month 1=Mar 12=Feb of next year, before 1752
							THEN ((CAST(SUBSTR(DATE, 3, 5) AS INT) + 10001 + (SUBSTR(DATE, 12, 1) = '/')) << 49)
						ELSE ((CAST(SUBSTR(DATE, 3, 5) AS INT) + 10000 + (SUBSTR(DATE, 12, 1) = '/')) << 49)
						END + -- Y1
					CASE 
						WHEN CAST(SUBSTR(DATE, 3, 5) AS INT) < 1752
							THEN CASE 
									WHEN ABS(SUBSTR(DATE, 8, 2)) = 0
										THEN 0
									WHEN ABS(SUBSTR(DATE, 8, 2)) < 11
										THEN ((ABS(SUBSTR(DATE, 8, 2) + 2)) << 45) -- SELECT 11%12
									WHEN ABS(SUBSTR(DATE, 8, 2)) > 10
										THEN ((ABS(SUBSTR(DATE, 8, 2) - 10)) << 45) -- SELECT Date,SUBSTR(Date,8,2)-10 FROM EventTable WHERE ABS(SUBSTR(Date,8,2))>10
									END
						ELSE (ABS(SUBSTR(DATE, 8, 2)) << 45)
						END + --M1 SELECT Date,((SUBSTR(Date,8,2)+2)%12) FROM EventTable
					(ABS(SUBSTR(DATE, 10, 2)) << 39) + --D1
					CASE SUBSTR(DATE, 14, 5)
						WHEN '+0000'
							THEN 17178820608 -- x'03 FF F0 00 00' or (2^34 - 2^20)  SELECT (1<<34)-(1<<20) 
						ELSE CASE 
								WHEN ABS(SUBSTR(DATE, 19, 2)) > 10
									AND CAST(SUBSTR(DATE, 14, 5) AS INT) < 1752
									THEN (CAST(SUBSTR(DATE, 14, 5) AS INT) + 10001) << 20
								ELSE (CAST(SUBSTR(DATE, 14, 5) AS INT) + 10000) << 20
								END
						END + --Y2
					CASE 
						WHEN CAST(SUBSTR(DATE, 14, 5) AS INT) < 1752
							THEN --(((SUBSTR(Date,19,2)+2)%12)<<16)
								CASE 
									WHEN ABS(SUBSTR(DATE, 19, 2)) = 0
										THEN 0
									WHEN ABS(SUBSTR(DATE, 19, 2)) < 11
										THEN ((ABS(SUBSTR(DATE, 19, 2) + 2)) << 16) -- SELECT 11%12
									WHEN ABS(SUBSTR(DATE, 19, 2)) > 10
										THEN ((ABS(SUBSTR(DATE, 19, 2) - 10)) << 16) -- SELECT Date,SUBSTR(Date,8,2)-10 FROM EventTable WHERE ABS(SUBSTR(Date,8,2))>10
									END
						ELSE (ABS(SUBSTR(DATE, 19, 2)) << 16)
						END + --M2
					(ABS(SUBSTR(DATE, 21, 2)) << 10) + --D2
					CASE SUBSTR(DATE, 2, 1) -- Flag
						WHEN 'B'
							THEN 0 --Before
						WHEN 'Y'
							THEN 3 --By
						WHEN 'T'
							THEN 6 --To
						WHEN 'U'
							THEN 9 --Until
						WHEN '.'
							THEN 12 --On
						WHEN 'R'
							THEN 15 --Between x and y
						WHEN 'S'
							THEN 18 --From x to y
						WHEN '-'
							THEN 21 --Hyphen x-y
						WHEN 'O'
							THEN 24 --x Or y
						WHEN 'F'
							THEN 1047579 --From    (27 + x'FFC00')
						WHEN 'I'
							THEN 1047582 --Since   (30 + x'FFC00')
						WHEN 'A'
							THEN 1047583 --After   (31 + x'FFC00')
						ELSE 0
						END
					--  WHEN '.' OR 'T' --blank or text date
			ELSE 9223372036854775807 --x'7F FF FF FF FF FF FF FF' (2^63-1)
			END AS CalcDate, SortDate, Details
	FROM EventTable
	)
WHERE Error <> 0
ORDER BY Error DESC;