-- MediaList.sql
-- 2011-11-06 by ve3meo
-- Lists all media and the key link ID to where used. Some SQLite managers will display thumbnail image (e.g. SQLite Expert).
-- The results displayed by some can be copied and pasted (or exported) to Excel which supports the synthesized Hyperlink column
-- for opening the media file.

SELECT OwnerID,
       CASE OwnerType
              WHEN 0
              THEN OwnerID
       END AS PersonID,
       CASE OwnerType
              WHEN 1
              THEN OwnerID
       END AS FamilyID,
       CASE OwnerType
              WHEN 2
              THEN OwnerID
       END AS EventID,
       CASE OwnerType
              WHEN 3
              THEN OwnerID
       END AS SourceID,
       CASE OwnerType
              WHEN 4
              THEN OwnerID
       END AS CitationID,
       CASE OwnerType
              WHEN 5
              THEN OwnerID
       END AS PlaceID,
       MediaID       ,
       Thumbnail,
       MediaPath     ,
       MediaFile     ,
       '=HYPERLINK("'
              ||MediaPath
              ||MediaFile
              ||'","Open")' AS HyperLink,
       Caption                          ,
       Description                      ,
       -- Convert Fact dates to readable form
       CASE SUBSTR( rawDate , 1 , 1 )
      	WHEN 'Q' THEN rawDate 
      	WHEN 'T' THEN Substr( rawDate , 2 , 20 ) 
      	WHEN 'D' THEN 
      		CASE Substr( rawDate , 2 , 1 ) 
      			WHEN 'A' THEN 'aft ' 
      			WHEN 'B' THEN 'bef ' 
      			WHEN 'F' THEN 'from ' 
      			WHEN 'I' THEN 'since ' 
      			WHEN 'R' THEN 'bet ' 
      			WHEN 'S' THEN 'from ' 
      			WHEN 'T' THEN 'to ' 
      			WHEN 'U' THEN 'until ' 
      			WHEN 'Y' THEN 'by ' 
      		ELSE '' 
      		END 
      		|| 
      		CASE Substr( rawDate , 13 , 1 ) 
      			WHEN 'A' THEN 'abt ' 
      			WHEN 'C' THEN 'ca ' 
      			WHEN 'E' THEN 'est ' 
      			WHEN 'L' THEN 'calc ' 
      			WHEN 'S' THEN 'say ' 
      			WHEN '6' THEN 'cert ' 
      			WHEN '5' THEN 'prob ' 
      			WHEN '4' THEN 'poss ' 
      			WHEN '3' THEN 'lkly ' 
      			WHEN '2' THEN 'appar ' 
      			WHEN '1' THEN 'prhps ' 
      			WHEN '?' THEN 'maybe ' 
      		ELSE '' 
      		END 
      		|| 
      		CASE WHEN Substr( rawDate , 3 , 1 ) = '-' THEN 'BC' ELSE '' END  
      		|| 
      		CASE WHEN Substr( rawDate , 4 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 4 , 4 ) END 
      		|| 
      		CASE WHEN Substr( rawDate , 12 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 3 , 5 ) ) ELSE '' END 
      		|| 
      		CASE 
      			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) <> '00' ) THEN '-??' 
      			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) == '00' ) THEN '' 
      			WHEN Substr( rawDate , 8 , 2 ) = '00' THEN '' 
      		ELSE '-' || Substr( rawDate , 8 , 2 ) 
      		END 
      		|| 
      		Coalesce( Nullif( '-' || Substr( rawDate , 10 , 2 ) , '-00' ) , '' ) 
      		|| 
      		CASE Substr( rawDate , 2 , 1 ) 
      			WHEN 'R' THEN ' and ' 
      			WHEN 'S' THEN ' to ' 
      			WHEN 'O' THEN ' or ' 
      			WHEN '-' THEN ' - ' 
      		ELSE '' 
      		END 
      		|| 
      		CASE Substr( rawDate , 24 , 1 ) 
      			WHEN 'A' THEN 'abt ' 
      			WHEN 'E' THEN 'est ' 
      			WHEN 'L' THEN 'calc ' 
      			WHEN 'C' THEN 'ca ' 
      			WHEN 'S' THEN 'say ' 
      			WHEN '6' THEN 'cert ' 
      			WHEN '5' THEN 'prob ' 
      			WHEN '4' THEN 'poss ' 
      			WHEN '3' THEN 'lkly ' 
      			WHEN '2' THEN 'appar ' 
      			WHEN '1' THEN 'prhps ' 
      			WHEN '?' THEN 'maybe ' 
    		  ELSE '' 
    	   	END 
      		|| 
      		CASE WHEN Substr( rawDate , 14 , 1 ) = '-' THEN 'BC' ELSE '' END 
      		|| 
      		CASE WHEN Substr( rawDate , 15 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 15 , 4 ) END 
      		|| 
      		CASE WHEN Substr( rawDate , 23 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 14 , 5 ) ) ELSE '' END 
      		|| 
      		CASE 
      			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) <> '00' ) THEN '-??' 
      			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) == '00' ) THEN '' 
      			WHEN Substr( rawDate , 19 , 2 ) = '00' THEN '' 
      		ELSE '-' || Substr( rawDate , 19 , 2 ) 
      		END 
      		|| 
      		Coalesce( Nullif( '-' || Substr( rawDate , 21 , 2 ) , '-00' ) , '' ) 
      	ELSE '' 
        END AS 'MediaDate' ,
--
       RefNumber         ,
       Include1 AS Scrpbk,
       IsPrimary
FROM   (
       -- All fields and values from MediaLinkTable and MultiMediaTable
       SELECT  MM.MediaID                 ,
               MM.MediaType               ,
               MM.MediaPath               ,
               MM.MediaFile COLLATE NOCASE,
               MM.URL                     ,
               MM.Thumbnail               ,
               ML.LinkID                  ,
               ML.MediaID                 ,
               ML.OwnerType               ,
               ML.OwnerID                 ,
               ML.IsPrimary               ,
               ML.Include1                ,
               ML.Include2                ,
               ML.Include3                ,
               ML.Include4                ,
               ML.SortOrder               ,
               ML.RectLeft                ,
               ML.RectTop                 ,
               ML.RectRight               ,
               ML.RectBottom              ,
               ML.Note                    ,
               ML.Caption COLLATE   NOCASE  ,
               ML.RefNumber COLLATE NOCASE  ,
               ML.Date AS rawDate           ,
               ML.SortDate                  ,
               CAST(ML.Description AS TEXT) AS Description
       FROM    MultiMediaTable              AS MM
               LEFT JOIN MediaLinkTable     AS ML
       USING   (MediaID)
       )
ORDER BY OwnerID ;