shape.format

attach database 'shape.sqlite3' as shape;


drop view latestNonDeletedArchEntFormattedIdentifiers;
CREATE VIEW latestNonDeletedArchEntFormattedIdentifiers as 
select uuid, aenttypeid, aenttypename, group_concat(response, '') as response, null as deleted 
from ( 
  select uuid, aenttypeid, aenttypename, group_concat(format(formatstring, vocabname, measure, freetext, certainty), appendcharacterstring) as response, null as deleted, aentcountorder 
  from ( 
    select uuid, aenttypeid, aenttypename, replace(replace(formatstring, char(10),''), char(13),'') as formatstring, vocabname, replace(replace(measure, char(13), '\r'), char(10), '\n') as measure, replace(replace(freetext, char(13), '\r'), char(10), '\n') as freetext, certainty, appendcharacterstring, null as deleted, aentcountorder, vocabcountorder, attributeid 
    from latestNonDeletedArchent 
      JOIN aenttype using (aenttypeid) 
      JOIN (select * from idealaent where isIdentifier='true') using (aenttypeid) 
      join attributekey  using (attributeid) 
      left outer join latestNonDeletedAentValue using (uuid, attributeid) 
      left outer join vocabulary using (attributeid, vocabid) 
    order by uuid, aentcountorder, vocabcountorder 
  ) 
  group by uuid, attributeid 
  having response is not null 
  order by uuid, aentcountorder) 
group by uuid 
order by uuid;



select uuid, aenttypename, attributename, coalesce(group_concat(response, ''),'') as response
  from (
        select uuid, aenttypeid, aenttypename, coalesce(group_concat(format(formatstring, devocab, measure, freetext, certainty), appendcharacterstring),'') as response, null as deleted, aentcountorder, attributeid, attributename
          from (
                select uuid, aenttypeid, aenttypename, replace(replace(formatstring, char(10),''), char(13),'') as formatstring, devocab, replace(replace(measure, char(13), '\r'), char(10), '\n') as measure, replace(replace(freetext, char(13), '\r'), char(10), '\n') as freetext, certainty, appendcharacterstring, null as deleted, aentcountorder, vocabcountorder, attributeid, attributename, vocabname
                  from latestNonDeletedArchent
                  JOIN aenttype using (aenttypeid)
                  JOIN idealaent using (aenttypeid)
                  join attributekey  using (attributeid)
                  left outer join latestNonDeletedAentValue using (uuid, attributeid)
                  left outer join (select vocabid, attributeid, coalesce(val, vocabname) as devocab, vocabcountorder, vocabname
                                  from vocabulary 
                                  left outer join shape.keyval on (vocabulary.vocabname = keyval.key))
                                  using (attributeid, vocabid)                                                
                 order by uuid, aentcountorder, vocabcountorder
                )
          group by uuid, attributeid
         having response is not null
          order by uuid, aentcountorder)
 group by uuid, attributeid
 order by uuid;
 
select uuid, aenttypename, 'identifier', response
from latestNonDeletedArchEntFormattedIdentifiers join createdModifiedAtBy using (uuid)
order by createdAt;



detach database shape;