/*
     populate_prl_title_table - take processed and reconst values
     from the title table and process them to:
        - reconstruct full title string
        - remove non-sort begin and end characters
        - insert diacritics and special characters as needed
        
     reconstruction procedure based on a stored procedure
     Copyright 2004 Dynix Corporation. All rights reserved.
     
     Modified April 2007 by Michael Silver to remove non-filing indicators
     and put in diacritics.
 */

set nocount on
go
declare title_curs cursor for
/* Modify select statement as needed */
    select  
            bib#
            , processed
            , reconst 
    from
            title
/* end of select statement */
go

 open title_curs

/* declare all needed variables */

   declare  @bib#           int,
            @processed      varchar(255),
            @reconst        varchar(255),
            @reconstructed  varchar(255),
            @reconst_pos    int,
            @new_char       int,
           @processed_pos  int,
           @reconstructed_pos  int,
           @reconst_len    int,
           @processed_len  int,
           @reconstructed_len   int,
           @achar          char(1),
           @before_char    char(1),
           @cur_char_value int,
           @next_char      int,
           @before_num     int,
           @after_char     char(1),
           @after_num      int

    /* read record */
        fetch title_curs into @bib#, @processed, @reconst
/* Big time while loop */
    while @@sqlstatus = 0
    begin

    /* set positional variables */
      select @reconst_pos = 1
      select @processed_pos = 0
    /* calculate length of processed and reconst columns */
      select @reconst_len = datalength(@reconst)
      select @processed_len = datalength(@processed)
      select @reconstructed = @processed
    /* start checking the reconst data one char at a time */
    while @reconst_pos < @reconst_len
      begin
    /* next character */
        select @reconst_pos = @reconst_pos + 1
    /* set current, previous and next characters (used in most reconst calculations)
    */
        select @achar = substring(@reconst,@reconst_pos,1)
        select @before_char = substring(@reconst,@reconst_pos-1,1)
        select @before_num  = ascii(@before_char)
        select @after_char  = substring(@reconst,@reconst_pos+1,1)
        select @after_num   = ascii(@after_char)
    /* start checking for the action characters I, M, R, 2, P, E, D */
    /* insert one character */
        if @achar = "I"
           begin
             select @reconstructed =   substring(@reconstructed, 1, @before_num - 1)
                                     + @after_char
                                     + substring(@reconstructed, @before_num, 255)
          /* increment position in reconst to jump past characters we have just used */
             select @reconst_pos = @reconst_pos + 2
             continue
           end
    /* insert many characters */
        if @achar = "M"
          begin
            select @reconstructed =   substring(@reconstructed, 1, @before_num - 1)
                                    + substring(substring(@reconst, @reconst_pos + 2, @after_num) + space(@after_num), 1, @after_num)
                                    + substring(@reconstructed, @before_num, 255)
         /* increment position in reconst to jump past characters we have just used */
            select @reconst_pos = @reconst_pos + 2 + @after_num
            continue
          end
    /* Replace */
        if @achar = "R"
          begin
            if @after_char is NULL
              select @after_char = " "
            select @reconstructed =   substring(@reconstructed, 1, @before_num - 1)
                                    + @after_char
                                    + substring(@reconstructed, @before_num + 1, 255)
         /* increment position in reconst to jump past characters we have just used */
            select @reconst_pos = @reconst_pos + 2
            continue
          end
    /* replace 2 */
        if @achar = "2"
          begin
            if @after_char = NULL
              select @after_char = " "
            select @reconstructed =   substring(@reconstructed, 1, @before_num - 1)
                                    + @after_char
                                    + substring(@reconstructed, @before_num + 2, 255)
         /* increment position in reconst to jump past characters we have just used */
            select @reconst_pos = @reconst_pos + 2
            continue
          end
    /* ReplacePair */
        if @achar = "P"
          begin
            select @reconstructed =   substring(@reconstructed, 1, @before_num - 1)
                                    + substring(substring(@reconst, @reconst_pos+1, 2) + "  ", 1, 2)
                                    + substring(@reconstructed, @before_num + 1, 255)
         /* increment position in reconst to jump past characters we have just used */
            select @reconst_pos = @reconst_pos + 3
            continue
          end
    /* delete */
        if @achar = "E"
          begin
            select @reconstructed =   substring(@reconstructed, 1, @before_num - 1)
                                    + substring(@reconstructed, @before_num + 1, 255)
         /* increment position in reconst to jump past characters we have just used */
            select @reconst_pos = @reconst_pos + 1
            continue
          end
    /* delete many */
        if @achar = "D"
          begin
            select @reconstructed =   substring(@reconstructed, 1, @before_num - 1)
                                    + substring(@reconstructed, @before_num + @after_num, 255)
         /* increment position in reconst to jump past characters we have just used */
            select @reconst_pos = @reconst_pos + 2
            continue
          end
    /* Clobber */
        if @achar = "C"
          begin
            select @reconstructed =   substring(@reconstructed, 1, @before_num - 1)
                                    + substring(@reconst, @reconst_pos+1, 255)
         /* increment position in reconst to jump past characters we have just used */
            select @reconst_pos = @reconst_len
            continue
          end
    /* end of while loop */
    end

/* Take out MARC non-filing indicators */
    if  charindex(char(136), @reconstructed) = 1 
        begin
            set @reconstructed = substring(@reconstructed, 2, 255)
            set @reconstructed = stuff(@reconstructed, charindex(char(137), @reconstructed), 1, NULL)
        end

/* Insert diacritics as appropriate */

    /* Set positional variables */
        select @reconstructed_pos = 0
    
    /* Calculate length of reconstructed variable */
        select @reconstructed_len = datalength(@reconstructed)

    /* Start checking the reconstructed data one character
        at a time
    */
        while @reconstructed_pos < @reconstructed_len
            begin

    /* next character */
            select @reconstructed_pos = @reconstructed_pos + 1

    /* set current and next characters (used in most diacritics) */
            select @cur_char_value = ascii(substring(@reconstructed, @reconstructed_pos, 1))
            select @next_char = ascii(substring(@reconstructed, @reconstructed_pos+1, 1))

    /* if it's a standard text character, do nothing */
            if @cur_char_value < 128
                begin
                    continue
                end

    /* if it's char(225) add a grave accent */
        if @cur_char_value = 225
            begin
                select @new_char = 
                    case @next_char
                        when  97 then 133 /* a */
                        when 101 then 138 /* e */
                        when 105 then 141 /* i */
                        when 111 then 149 /* o */
                        when 117 then 151 /* u */
                        when  65 then 183 /* A */
                        when  69 then 212 /* E */
                        when  73 then 222 /* I */
                        when  79 then 227 /* letter O */
                        when  85 then 235 /* U */
                        else @next_char
                    end
                select @reconstructed = stuff(@reconstructed, @reconstructed_pos, 2, char(@new_char))
                select @reconstructed_len = @reconstructed_len - 1
                continue
            end

    /* if it's char(226) add an acute accent  */
        if @cur_char_value = 226
            begin
                select @new_char = 
                    case @next_char
                        when  97 then 160 /* a */
                        when 101 then 130 /* e */
                        when 105 then 161 /* i */
                        when 111 then 162 /* o */
                        when 117 then 163 /* u */
                        when 121 then 236 /* y */
                        when  65 then 181 /* A */
                        when  69 then 144 /* E */
                        when  73 then 214 /* I */
                        when  79 then 224 /* letter O */
                        when  85 then 233 /* U */
                        when  89 then 237 /* Y */
                        else @next_char
                    end
                select @reconstructed = stuff(@reconstructed, @reconstructed_pos, 2, char(@new_char))
                select @reconstructed_len = @reconstructed_len - 1
                continue
            end

    /* if it's char(227) add a circumflex  */
        if @cur_char_value = 227
            begin
                select @new_char = 
                    case @next_char
                        when  97 then 131 /* a */
                        when 101 then 136 /* e */
                        when 105 then 140 /* i */
                        when 111 then 147 /* o */
                        when 117 then 150 /* u */
                        when  65 then 182 /* A */
                        when  69 then 210 /* E */
                        when  73 then 215 /* I */
                        when  79 then 226 /* letter O */
                        when  85 then 234 /* U */
                        else @next_char
                    end
                select @reconstructed = stuff(@reconstructed, @reconstructed_pos, 2, char(@new_char))
                select @reconstructed_len = @reconstructed_len - 1
                continue
            end

    /* if it's char(228) add a tilde */
        if @cur_char_value = 228
            begin
                select @new_char = 
                    case @next_char
                        when  97 then 198 /* a */
                        when 110 then 164 /* n */
                        when 111 then 228 /* o */
                        when  65 then 199 /* A */
                        when  78 then 165 /* N */
                        when  79 then 229 /* letter O */
                        else @next_char
                    end
                select @reconstructed = stuff(@reconstructed, @reconstructed_pos, 2, char(@new_char))
                select @reconstructed_len = @reconstructed_len - 1
                continue
            end

    /* if it's char(232) add a dieresis or umlaut */
        if @cur_char_value = 232
            begin
                select @new_char = 
                    case @next_char
                        when  97 then 132 /* a */
                        when 101 then 137 /* e */
                        when 105 then 139 /* i */
                        when 111 then 148 /* o */
                        when 117 then 129 /* u */
                        when 121 then 152 /* y */
                        when  65 then 142 /* A */
                        when  69 then 211 /* E */
                        when  73 then 216 /* I */
                        when  79 then 153 /* letter O */
                        when  85 then 154 /* U */
                        else @next_char
                    end
                select @reconstructed = stuff(@reconstructed, @reconstructed_pos, 2, char(@new_char))
                select @reconstructed_len = @reconstructed_len - 1
                continue
            end

    /* if it's char(234) add an angstrom (ring above) */
        if @cur_char_value = 234
            begin
                select @new_char = 
                    case @next_char
                        when  97 then 134 /* a */
                        when  65 then 143 /* A */
                        else @next_char
                    end
                select @reconstructed = stuff(@reconstructed, @reconstructed_pos, 2, char(@new_char))
                select @reconstructed_len = @reconstructed_len - 1
                continue
            end

    /* if it's char(240) add a cedilla */
        if @cur_char_value = 240
            begin
                select @new_char = 
                    case @next_char
                        when  99 then 135 /* c */
                        when  67 then 128 /* C */
                        else @next_char
                    end
                select @reconstructed = stuff(@reconstructed, @reconstructed_pos, 2, char(@new_char))
                select @reconstructed_len = @reconstructed_len - 1
                continue
            end


    /* if it's a special character, replace it with the right char */
        select @new_char = 
            case @cur_char_value
                when 162 then 157 /* uppercase slash letter o */
                when 163 then 209 /* uppercase D with crossbar */
                when 164 then 232 /* uppercase thorn */
                when 165 then 146 /* uppercase digraph AE */
                when 170 then 169 /* registered symbol r in circle */
                when 171 then 241 /* plus/minus sign */
                when 178 then 155 /* lowercase slash letter o */
                when 180 then 231 /* lowercase thorn */
                when 181 then 145 /* lowercase digraph ae */
                when 185 then 156 /* British pound symbol */
                when 186 then 208 /* lowercase eth */
                when 192 then 248 /* degree symbol */
                when 195 then 184 /* copyright symbol c in circle */
                when 199 then 225 /* lowercase sharp s (eszett) */
                else @cur_char_value
            end
        if @new_char != @cur_char_value
            select @reconstructed = stuff(@reconstructed, @reconstructed_pos, 1, char(@new_char))
        continue
        end

/* insert values into the prl_title table */
    insert into prl_title(bib#, title_reconstructed) values (@bib#, @reconstructed)

/* read next record */
    fetch title_curs into @bib#, @processed, @reconst

end

go