etlQuery ; 
 ; ----------------------------------------------------------------------------
 ; #
 ; #  Chronologica 
 ; #
 ; #  http://etimeline.com/
 ; #
 ; #  Copyright:
 ; #    Copyright 2011 eTimeline, LLC. All rights reserved.
 ; #
 ; #  License:
 ; #    See the etlLicense.txt file in the project's top-level directory for details.
 ; #
 ; #  Authors:
 ; #    * Andy White
 ; #
 ; ----------------------------------------------------------------------------
 ;
 ;
composeSpouse n spid,subquery,splist,now,cstate,spdata,i
 s subquery(0,"Field")="etlName"
 s subquery(0,"Format")=select(spos,"Format")
 s now=$s(when="":$$whenNow^dcTimeUtil(),1:when)
 ;
 w StartList
 ;
 ; get spouses I linked to
 ;
 d get^dcDb(.spdata,"etlPerson",id,"etlSpouse",when,"",historical)
 ;
 f  s i=$o(spdata(i)) q:i=""  d
 . s spid=spdata(i),splist(spid)=1
 . q:'$$isCurrent^dcDb("etlPerson",spid,when,historical)
 . ;
 . i compact d writeCompactFld^dcDbQuery("etlPerson",spid,now,.subquery,0,1,historical) q
 . d writeFullFld^dcDbQuery("etlPerson",spid,now,.subquery,0,1,historical)
 ;
 ; get spouses linked to me
 ;
 f  s spid=$$loopIndex^dcDb("etlPerson","etlSpouse",id,.cstate,when,historical) q:spid=""  d
 . q:splist(spid) 
 . s splist(spid)=1
 . q:'$$isCurrent^dcDb("etlPerson",spid,when,historical)
 . ;
 . i compact d writeCompactFld^dcDbQuery("etlPerson",spid,now,.subquery,0,1,historical) q
 . d writeFullFld^dcDbQuery("etlPerson",spid,now,.subquery,0,1,historical)
 ;
 w EndList
 ;
 quit
 ;
 ; d local^dcConn("QUERY etlListSpouses")
 ;
listSpouses n id,compact,when,historical
 s id=Params("PersonId"),compact=0,historical=1
 i id="" d err^dcConn(100100) quit
 q:'$$isCurrent^dcDb("etlPerson",id,when,historical)
 ;
 ;
composeSpouse2 n sid,spid,stamp,subquery,splist,f2,spdata,i,from,to,now
 s subquery(0,"Field")="etlName"
 s subquery(0,"Format")=select(spos,"Format")
 s now=$s(when="":$$whenNow^dcTimeUtil(),1:when)
 ;
 w StartList
 ;
 ; get spouses I linked to
 ;
 d getDeep^dcDb(.spdata,"etlPerson",id,"etlSpouse",when,historical)
 ;
 f  s i=$o(spdata(i)) q:i=""  d
 . s spid=spdata(i,"Data",0),splist(spid)=1
 . q:'$$isCurrent^dcDb("etlPerson",spid,when,historical)
 . ; 
 . s f2=spdata(i,"From") s:f2="" f2=1
 . s splist(spid,f2)=1    ; mark so we do not repeat
 . s from=spdata(i,"From"),to=spdata(i,"To")
 . ;
 . w StartRec
 . w Field_"Id"_ScalarId_spid
 . i from'="" w Field_"From"_ScalarBigDateTime_from
 . e  w Field_"From"_ScalarNull
 . i to'="" w Field_"To"_ScalarBigDateTime_to
 . e  w Field_"To"_ScalarNull
 . w Field_"Name"
 . d writeCompactFld^dcDbQuery("etlPerson",spid,now,.subquery,0,1,historical) 
 . ;
 . w EndRec
 ;
 ; get spouses linked to me
 ;
 s spid=""
 f  s spid=$o(^Index2("etlPerson","etlSpouse",id,spid)) q:spid=""  d
 . q:'$$isCurrent^dcDb("etlPerson",spid,when,historical)
 . f  s sid=$o(^Index2("etlPerson","etlSpouse",id,spid,sid)) q:sid=""  d
 . . s stamp=$$getStamp4^dcDb("etlPerson",spid,"etlSpouse",sid,when) q:stamp="" 
 . . ;
 . . s f2=^Record("etlPerson",spid,"etlSpouse",sid,stamp,"From") s:f2="" f2=1
 . . q:splist(spid,f2)   ; do not repeat a marriage, even if back linked by partner
 . . ;
 . . s from=^Record("etlPerson",spid,"etlSpouse",sid,stamp,"From")
 . . s to=^Record("etlPerson",spid,"etlSpouse",sid,stamp,"To")
 . . ;
 . . w StartRec
 . . w Field_"Id"_ScalarId_spid
 . . i from'="" w Field_"From"_ScalarBigDateTime_from
 . . e  w Field_"From"_ScalarNull
 . . i to'="" w Field_"To"_ScalarBigDateTime_to
 . . e  w Field_"To"_ScalarNull
 . . w Field_"Name"
 . . d writeCompactFld^dcDbQuery("etlPerson",spid,now,.subquery,0,1,historical) 
 . . ;
 . . w EndRec
 ;
 w EndList
 ;
 quit
 ;
 ; d local^dcConn("QUERY etlSearchBDay")
 ;
srchBDay n spid,to,from,now,subquery,cstate
 s from=Params("From"),to=Params("To"),now=$$whenNow^dcTimeUtil(),subquery(0,"Field")="etlName"
 ;
 w StartList
 ;
 f  s spid=$$loopRange^dcDb("etlPerson","From",from,to,.cstate,"") q:spid=""  d
 . d writeCompactFld^dcDbQuery("etlPerson",spid,now,.subquery,0,1,1) 
 ;
 w EndList
 ;
 quit
 ;
 ; d local^dcConn("QUERY etlListDescendants")
 ;
listDesc n id,compact,when
 s id=Params("PersonId"),compact=0
 i id="" d err^dcConn(100100) quit
 ;
 ;
composeDescends n subquery,now
 s subquery(0,"Field")="etlName"
 s subquery(1,"Field")="From"
 s subquery(2,"Field")="To"
 s now=$s(when="":$$whenNow^dcTimeUtil(),1:when)
 ;
 d cmpDesc(id,5)
 ;
 quit
 ;
 ;
 ;
cmpDesc(id,lvl) n spid,cfld
 q:'$$isCurrent^dcDb("etlPerson",id,when,1)
 ;
 w StartRec
 ;
 w Field_"Id"_ScalarId_id
 ;
 w Field_"Name"
 d writeCompactFld^dcDbQuery("etlPerson",id,now,.subquery,0,1,1) 
 ;
 w Field_"DOB"
 d writeCompactFld^dcDbQuery("etlPerson",id,now,.subquery,1,1,1) 
 ;
 w Field_"DOD"
 d writeCompactFld^dcDbQuery("etlPerson",id,now,.subquery,2,1,1) 
 ;
 i lvl>0 d
 . w Field_"Children"_StartList
 . ;
 . f cfld="etlBioMother","etlBioFather"  d
 . . f  s spid=$o(^Index1("etlPerson",cfld,id,spid)) q:spid=""  d cmpDesc(spid,lvl-1)
 . ;
 . w EndList
 ;
 w EndRec
 ;
 quit
 ;
 ; d local^dcConn("QUERY etlListAll")
 ;
listAll n compact,now,select,id s compact=Params("Compact"),now=$$whenNow^dcTimeUtil()
 ;
 s select(0,"Field")="Id" 
 s select(1,"Field")="etlBioFather" 
 s select(1,"Name")="Father" 
 s select(1,"Select",0,"Field")="etlName" 
 s select(1,"Select",0,"Name")="Name" 
 s select(1,"Select",1,"Field")="From" 
 s select(1,"Select",1,"Name")="DOB" 
 s select(1,"Select",2,"Field")="etlOccupation" 
 s select(1,"Select",2,"Name")="Occupations" 
 s select(2,"Field")="etlBioMother" 
 s select(2,"Name")="Mother" 
 s select(2,"Select",0,"Field")="etlName" 
 s select(2,"Select",0,"Name")="Name" 
 s select(2,"Select",1,"Field")="From" 
 s select(2,"Select",1,"Name")="DOB" 
 s select(2,"Select",2,"Field")="etlOccupation" 
 s select(2,"Select",2,"Name")="Occupations" 
 s select(4,"Field")="From" 
 s select(4,"Name")="DOB" 
 s select(5,"Field")="To" 
 s select(5,"Name")="DOD" 
 s select(7,"Field")="etlName" 
 s select(7,"Name")="Name" 
 s select(8,"Field")="etlOccupation" 
 s select(8,"Name")="Occupations" 
 s select(9,"Field")="etlSex" 
 s select(9,"Name")="Sex" 
 s select(9,"Format")="Tr" 
 ;
 w StartList
 ;
 f  s id=$o(^Record("etlPerson",id)) q:id=""  d writeRec^dcDbQuery("etlPerson",id,now,.select,2,compact,0,1) 
 ;
 w EndList
 ;
 quit
 ;
 ;
 ; record composer
recage n age s age=$$getage(table,id)
 i age="" w ScalarNull
 e  w ScalarInt_age
 quit
 ;
 ;
 ; select composer
selage() n age s age=$$getage(table,id)
 i age="" quit ScalarNull
 quit ScalarInt_age
 ;
 ;
 ; where composer
whrage(lst) s lst(0)=$$getage(table,id)
 quit 
 ;
 ;
 ; simplistic, only looks at year, not month or day
getage(table,id) n dob,dod,year,age 
 s dob=$$get1^dcDb(table,id,"From") quit:dob="" ""
 s dob=$e(dob,2,12)
 ;
 s dod=$$get1^dcDb(table,id,"To")
 i (dod'="")&(when]]dod) s year=dod
 e  i when'="" s year=when 
 e  s year=$$whenNow^dcTimeUtil()
 ;
 s year=$e(year,2,12)
 ;
 s age=year-dob
 s:age<0 age=0
 quit age
 ;
 ;
 ;
allmen() n id,fnd s id=cstate("id") 
 ;
 f  s id=$o(^Record(table,id)) q:id=""  d  q:fnd
 . s:$$get3^dcDb(table,id,"etlSex",when,"",1)="Male" fnd=1
 ;
 s cstate("id")=id
 ;
 quit id
 ;
 ;
 ;