-- Names-ConcordanceCapsFirstYrsMids.sql
-- 2014-11-27 Tom Holden ve3meo
/*
Adapted from Names-ConcordanceCaps.sql

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 (1870-1936) Eliza
Annie Eliza Alexander | ALEXANDER: Annie (1870-1936) Eliza
B. F. ALEXANDER	      | ALEXANDER: B. (1838-1925) F.
B. F. Alexander	      | ALEXANDER: B. (1838-1925) F.
Betsy ALEXANDER	      | ALEXANDER: Betsy (1807-1866)
Betsy Alexander	      | ALEXANDER: Betsy (1807-1866)

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 of the individuals.

Each Name from the NameTable is entered twice, once with surname 
forced to upper case, once as entered. A display 
setting in RootsMagic File Options for the database causes 
the standard reports to output upper case surnames; this 
Concordance covers both possibilities. 

If you do not want the Index to have all-cap surnames,
remove the UPPER() function from the IndexName expression, 
leaving intact what is between the parentheses.
*/

-- parse Givens into First and Middles; create person's era string
DROP VIEW IF EXISTS NameGivensParsed
;

CREATE TEMP VIEW NameGivensParsed
AS
SELECT 
  NameID,   
  CASE INSTR(Given, ' ') > 0
  WHEN 1 
  THEN TRIM(SUBSTR(Given,1,INSTR(Given, ' ')))
  ELSE Given
  END AS FirstName, 
  CASE INSTR(Given, ' ') > 0
  WHEN 1 
  THEN TRIM(SUBSTR(Given,INSTR(Given, ' '),99))
  ELSE ''
  END AS MiddleNames,
  CASE 
  WHEN BirthYear + DeathYear = 0
  THEN ''
  WHEN BirthYear = 0
  THEN '(d' || DeathYear || ')'
  WHEN DeathYear = 0
  THEN '(b' || BirthYear || ')'
  ELSE '(' || BirthYear || '-' || DeathYear || ')'
  END AS Era
        
FROM NameTable
;

-- make concordance table
DROP VIEW IF EXISTS NameConcordanceCapsFirstYrsMids
;
CREATE TEMP VIEW NameConcordanceCapsFirstYrsMids
AS
SELECT *
FROM
(
SELECT DISTINCT
  REPLACE
  (
   TRIM
   (
    Prefix || ' ' 
    || Given || ' ' 
    || UPPER(Surname) || ' ' 
    || Suffix
    )
    , '  ', ' '
   )
  || '-' || OwnerID  
  AS SearchName
  , 
  REPLACE
  (
   TRIM
   (UPPER(CASE Surname WHEN '' THEN '<No Surname>' ELSE Surname END) || ': '
    || FirstName 
    || CASE Era WHEN '' THEN '' ELSE ' ' || Era || ' ' END
    || Middlenames || ' ' 
    || Prefix || ' ' 
    || Suffix
    )
    , '  ', ' '
   ) 
  AS IndexName
FROM NameTable N 
INNER JOIN NameGivensParsed FM USING(NameID)

UNION ALL
-- search Surname not forced to upper case
SELECT DISTINCT
  REPLACE
  (
   TRIM
   (
    Prefix || ' ' 
    || Given || ' ' 
    || Surname || ' ' 
    || Suffix
    )
    , '  ', ' '
   )
  || '-' || OwnerID  
  AS SearchName
  , 
  REPLACE
  (
   TRIM
   (UPPER(CASE Surname WHEN '' THEN '<No Surname>' ELSE Surname END) || ': '
    || FirstName 
    || CASE Era WHEN '' THEN '' ELSE ' ' || Era || ' ' END
    || Middlenames || ' ' 
    || Prefix || ' ' 
    || Suffix
    )
    , '  ', ' '
   ) 
  AS IndexName
FROM NameTable N 
INNER JOIN NameGivensParsed FM USING(NameID)
)
ORDER BY IndexName
;

/*
Click on the View NameConcordanceCapsFirstYrsMids to see the resulting
ConcordanceTable.

Select all results, copy and paste into a new, blank MS Word document. 
You should see the table in Word; it is okay to leave the Headings
as-is. 
Save the Word document to where you can find it to use with
Index Automarking in your RootsMagic report or book.
*/