-- Names-ConcordanceCaps.sql
-- 2014-11-25 Tom Holden ve3meo
/*
Creates a temporary SQLite View laid out as a MS Word
Concordance Table of people's names to aid in the generation
of indexes for a collection of Individual Summary reports. 
It is of the form:
SearchName	          | IndexName
Annie Eliza ALEXANDER | ALEXANDER: Annie Eliza
B. F. ALEXANDER	      | ALEXANDER: B. F.
Betsy ALEXANDER	      | ALEXANDER: Betsy

The left column contains the case-sensitive search string; 
the right column has the value to be outputted in the Index.
In this example, the colon will cause one ALEXANDER surname 
to be printed with an indented line for each os the individuals.

The search surnames have been uppercased because a display 
setting in RootsMagic File Options for the database causes 
the standard reports to output upper case surnames; that is
what Word will search. If your reports have lower case surnames,
remove the UPPER() function from the SearchName expression.

Likewise, if you do not want the Index to have all-cap surnames,
remove the UPPER() function from the IndexName expression.
*/
DROP VIEW IF EXISTS NameConcordanceCaps
;
CREATE TEMP VIEW NameConcordanceCaps
AS
SELECT DISTINCT
  REPLACE
  (
   TRIM
   (
    Prefix || ' ' 
    || Given || ' ' 
    || UPPER(Surname) || ' ' 
    || Suffix
    )
    , '  ', ' '
   ) 
  AS SearchName
  , 
  REPLACE
  (
   TRIM
   (UPPER(CASE Surname WHEN '' THEN 'UNKNOWN' ELSE Surname END) || ': ' 
    || Prefix || ' ' 
    || Given || ' ' 
    || Suffix
    )
    , '  ', ' '
   ) 
  AS IndexName
FROM NameTable
ORDER BY IndexName
;