dcDbQuery ;
 ;
 ; ----------------------------------------------------------------------------
 ; #
 ; #  DivConq
 ; #
 ; #  http://divconq.com/
 ; #
 ; #  Copyright:
 ; #    Copyright 2012 eTimeline, LLC. All rights reserved.
 ; #
 ; #  License:
 ; #    See the dcLicense.txt file in the project's top-level directory for details.
 ; #
 ; #  Authors:
 ; #    * Andy White
 ; #
 ; ----------------------------------------------------------------------------
 ;
 ; Request
 ;
 ; d local^dcConn("QUERY dcLoadRecord")
 ;
 ;  Table	name
 ;  Id		record id
 ;  Filter	filter name
 ;  Extra	parameters for filter
 ;
 ;  Select	list of fields to query
 ;			0,
 ;				"Field"			name
 ;				"Format"		format of field if scalar
 ;				"Name"			display name of field
 ;				"ForeignField"	value field in fk relationship
 ;				"Composer"		composer script to generate content
 ;				"Select"		list of fields to query, see above
 ;			1...
 ;
 ;	When	"" = all
 ;			[stamp] = on or before this time
 ;
 ;	Compact	true / false
 ;
 ;	Historical	true / false
 ;
 ; Result
 ;
 ;		========== return compact============
 ;		{
 ;			{ssfield}: {value},
 ;			{slfield}: [
 ;				{value},
 ;				{value}
 ;			],
 ;			{dsfield}: {value},				if not When = ""
 ;			{dsfield}: [					if When = ""
 ;				{value},
 ;				{value}
 ;			],
 ;			{dlfield}: [
 ;				{value},
 ;				{value}
 ;			]
 ;		}
 ;
 ;
 ;		========== return full ============
 ;		{
 ;			{ssfield}: {
 ;				Data: {value},
 ;				Tags: {tags}
 ;			},
 ;			{slfield}: [
 ;				{
 ;					Sid: {sid},
 ;					Data: {value},
 ;					Tags: {tags}
 ;				},
 ;				{
 ;					Sid: {sid},
 ;					Data: {value},
 ;					Tags: {tags}
 ;				}
 ;			],
 ;			{dsfield}:  {					if not When = ""
 ;					Sid: {sid},
 ;					From: {from},
 ;					Data: {value},
 ;					Tags: {tags}
 ;			},
 ;			{dsfield}: [					if When = ""
 ;				{
 ;					Sid: {sid},
 ;					From: {from},
 ;					Data: {value},
 ;					Tags: {tags}
 ;				},
 ;				{
 ;					Sid: {sid},
 ;					From: {from},
 ;					Data: {value},
 ;					Tags: {tags}
 ;				}
 ;			],
 ;			{dlfield}: [
 ;				{
 ;					Sid: {sid},
 ;					From: {from},
 ;					To: {to},
 ;					Data: {value},
 ;					Tags: {tags}
 ;				},
 ;				{
 ;					Sid: {sid},
 ;					From: {from},
 ;					To: {to},
 ;					Data: {value},
 ;					Tags: {tags}
 ;				}
 ;			]
 ;		}
 ;
 ;
 ;	========== FK =============
 ;  normally a {value} is a scalar, unless an FK is invloved - if so then a subquery result may be present:
 ;
 ; scalar w/o FK
 ; 
 ;		{ssfield}: {value},
 ;		{slfield}: [
 ;			{value},
 ;			{value}
 ;		],
 ;
 ; scalar w/ FK to scalar
 ; 
 ;		{ssfield}: {
 ;			{field}: {value},
 ;			etc
 ;		},
 ;		{slfield}: [
 ;			{
 ;				{field}: {value},
 ;				etc
 ;			},
 ;			{
 ;				{field}: {value},
 ;				etc
 ;			}
 ;		],
 ;
 ;
loadRec n id,table,when,compact,select,historical
 s id=Params("Id"),table=Params("Table"),when=Params("When")
 s compact=Params("Compact"),historical=Params("Historical")
 i (id="")!(table="") d err^dcConn(50012) quit
 ;
 d runFilter^dcDbUpdate("Query") quit:Errors  ; if any violations in filter then do not proceed
 ;
 s:$d(Params("Select"))=0 Params("Select")="*"
 m select=Params("Select")
 ;
 d writeRec(table,id,when,.select,20,compact,0,historical) 
 ;
 quit 
 ;
 ;
 ; skrec = skip record output, for when write Rec is already handled
writeRec(table,id,when,select,lvl,compact,skrec,historical) n fname,i,spos,field,stamp,composer,retired
 i id="" quit
 i '$$isCurrent^dcDb(table,id,when,historical) quit
 ;
 ; create the select array ("Id" is a special field, even though not in fields list):
 ;   select([position],"Field")=[field name(s), colon separated]
 ;   select([position],"Format")=[formatting]
 ;   select([position],"Name")=[return name]
 ;
 i select="*" d  
 . s select(0,"Field")="Id",select(0,"Name")="Id"
 . f i=1:1  s fname=$o(^Schema(table,"Fields",fname)) q:fname=""  d
 . . s select(i,"Field")=fname,select(i,"Name")=fname
 ;
 i 'skrec w StartRec
 ;
 f  s spos=$o(select(spos))  q:spos=""  d
 . w Field_$s(select(spos,"Name")'="":select(spos,"Name"),1:select(spos,"Field"))
 . i compact d writeCompactFld(table,id,when,.select,spos,lvl,historical) q
 . d writeFullFld(table,id,when,.select,spos,lvl,historical)
 ;
 i 'skrec w EndRec	
 ;
 quit
 ;
 ;
writeCompactFld(table,id,when,select,spos,lvl,historical) n field,stamp,fschema,islist,pos,subquery,sid,to,from,tidx,spid,tbl,kfld
 i $d(select(spos,"Composer")) d  quit
 . s composer=select(spos,"Composer")
 . i (composer="")!(^Composer1(composer)="") w ScalarNull
 . x "d "_^Composer1(composer)
 ;
 s field=select(spos,"Field")
 i field="" w ScalarNull quit
 ;
 i field="Id" d  quit
 . i $d(select(spos,"Table")) d  q
 . . s tbl=select(spos,"Table"),kfld=select(spos,"KeyField")
 . . ;
 . . m fschema=^Schema(tbl,"Fields",kfld)
 . . ;
 . . s subquery(0,"Field")=select(spos,"ForeignField")
 . . s subquery(0,"Format")=select(spos,"Format")
 . . ;
 . . w StartList
 . . ;
 . . i 'fschema("List")&'fschema("Dynamic") d  i 1
 . . . f  s spid=$o(^Index1(tbl,kfld,id,spid)) q:spid=""  d
 . . . . q:'$$isCurrent^dcDb(tbl,spid,when,historical)
 . . . . d writeCompactFld(tbl,spid,when,.subquery,0,1,historical) 
 . . e  f  s spid=$o(^Index2(tbl,kfld,id,spid)) q:spid=""  d
 . . . q:'$$isCurrent^dcDb(tbl,spid,when,historical)
 . . . d writeCompactFld(tbl,spid,when,.subquery,0,1,historical) 
 . . ;
 . . w EndList
 . ;
 . w ScalarId_$$format(table,field,id,select(spos,"Format")) 
 ;
 m fschema=^Schema(table,"Fields",field)
 i $d(fschema)=0 w ScalarNull quit
 ;
 ; DynamicList, StaticList - also DynamicStatic when All (when="")
 ;
 i ((when="")&fschema("Dynamic"))!fschema("List") d  quit
 . w StartList
 . ;
 . f  s sid=$o(^Record(table,id,field,sid)) q:sid=""  d
 . . s stamp=$$getStamp4^dcDb(table,id,field,sid,when) q:stamp="" 
 . . ;
 . . s spid=$s($d(fschema("ForeignKey")):^Record(table,id,field,sid,stamp,"Data",0),1:"")
 . . i (spid'="")&$$writeCompactForeign(fschema("ForeignKey"),spid,when,.select,spos,lvl,historical) q
 . . ;
 . . w $$getTypeFor(fschema("Type"))
 . . f  s pos=$o(^Record(table,id,field,sid,stamp,"Data",pos)) q:pos=""  d
 . . . w $$format(table,field,^Record(table,id,field,sid,stamp,"Data",pos),select(spos,"Format"))
 . ;
 . w EndList
 ;
 ; DynamicStatic
 ;
 i fschema("Dynamic") d  quit
 . s sid=$$get3sid^dcDb(table,id,field,when,"",historical) 
 . s stamp=$$getStamp3^dcDb(table,id,field,sid,when,historical)
 . i stamp="" w ScalarNull q
 . ;
 . s spid=$s($d(fschema("ForeignKey")):^Record(table,id,field,sid,stamp,"Data",0),1:"")
 . i (spid'="")&$$writeCompactForeign(fschema("ForeignKey"),spid,when,.select,spos,lvl,historical) q
 . ;
 . w $$getTypeFor(fschema("Type"))
 . f  s pos=$o(^Record(table,id,field,sid,stamp,"Data",pos)) q:pos=""  d
 . . w $$format(table,field,^Record(table,id,field,sid,stamp,"Data",pos),select(spos,"Format"))
 ;
 ; StaticScalar
 ;
 s stamp=$$getStamp1^dcDb(table,id,field) i stamp="" w ScalarNull quit
 ;
 s spid=$s($d(fschema("ForeignKey")):^Record(table,id,field,stamp,"Data",0),1:"")
 i (spid'="")&$$writeCompactForeign(fschema("ForeignKey"),spid,when,.select,spos,lvl,historical) quit
 ;
 w $$getTypeFor(fschema("Type"))
 f  s pos=$o(^Record(table,id,field,stamp,"Data",pos)) q:pos=""  d
 . w $$format(table,field,^Record(table,id,field,stamp,"Data",pos),select(spos,"Format"))
 ;
 quit
 ;
 ;
 ; return true if the value is consumed (written or ignored)
writeCompactForeign(table,id,when,select,spos,lvl,historical) n subquery
 q:'$$isCurrent^dcDb(table,id,when,historical) 1
 ;
 i (lvl>1)&$d(select(spos,"Select")) d  quit 1
 . m subquery=select(spos,"Select")
 . d writeRec(table,id,when,.subquery,lvl-1,1,0,historical) 
 ;
 i $d(select(spos,"ForeignField")) d  quit 1
 . s subquery(0,"Field")=select(spos,"ForeignField")
 . s subquery(0,"Format")=select(spos,"Format")
 . d writeCompactFld(table,id,when,.subquery,0,1,historical) 
 ;
 quit 0
 ;
 ;
 ;
writeFullFld(table,id,when,select,spos,lvl,historical) n field,stamp,fschema,islist,pos,subquery,sid,to,from,tidx,tbl,spid,kfld
 i $d(select(spos,"Composer")) d  quit
 . s composer=select(spos,"Composer")
 . i (composer="")!(^Composer1(composer)="") w ScalarNull
 . x "d "_^Composer1(composer)
 ;
 s field=select(spos,"Field")
 i field="" w ScalarNull quit
 ;
 i field="Id" d  quit
 . i $d(select(spos,"Table")) d  q
 . . s tbl=select(spos,"Table"),kfld=select(spos,"KeyField")
 . . ;
 . . m fschema=^Schema(tbl,"Fields",kfld)
 . . ;
 . . s subquery(0,"Field")=select(spos,"ForeignField")
 . . s subquery(0,"Format")=select(spos,"Format")
 . . ;
 . . w StartList
 . . ;
 . . i 'fschema("List")&'fschema("Dynamic") d  i 1
 . . . f  s spid=$o(^Index1(tbl,kfld,id,spid)) q:spid=""  d
 . . . . q:'$$isCurrent^dcDb(tbl,spid,when,historical)
 . . . . d writeFullFld(tbl,spid,when,.subquery,0,1,historical) 
 . . e  f  s spid=$o(^Index2(tbl,kfld,id,spid)) q:spid=""  d
 . . . q:'$$isCurrent^dcDb(tbl,spid,when,historical)
 . . . d writeFullFld(tbl,spid,when,.subquery,0,1,historical) 
 . . ;
 . . w EndList
 . ;
 . w StartRec_Field_"Data"_ScalarId_$$format(table,field,id,select(spos,"Format"))_EndRec
 ;
 m fschema=^Schema(table,"Fields",field)
 i $d(fschema)=0 quit
 ;
 ; DynamicList, StaticList - also DynamicStatic when All (when="")
 ;
 i ((when="")&fschema("Dynamic"))!fschema("List") d  quit
 . w StartList
 . ;
 . f  s sid=$o(^Record(table,id,field,sid)) q:sid=""  d
 . . s stamp=$$getStamp4^dcDb(table,id,field,sid,when) q:stamp="" 
 . . ;
 . . s spid=$s($d(fschema("ForeignKey")):^Record(table,id,field,sid,stamp,"Data",0),1:"")
 . . i (spid'="")&$$writeFullForeign(fschema("ForeignKey"),spid,when,.select,spos,lvl,historical) i 1
 . . e  d writeFull2(table,id,field,sid,stamp,fschema("Type"),select(spos,"Format"))
 . ;
 . w EndList
 ;
 ; DynamicStatic
 ;
 i fschema("Dynamic") d  quit
 . s sid=$$get3sid^dcDb(table,id,field,when,"",historical) 
 . s stamp=$$getStamp3^dcDb(table,id,field,sid,when,historical)
 . i stamp="" w ScalarNull q
 . ;
 . s spid=$s($d(fschema("ForeignKey")):^Record(table,id,field,sid,stamp,"Data",0),1:"")
 . i (spid'="")&$$writeFullForeign(fschema("ForeignKey"),spid,when,.select,spos,lvl,historical) i 1
 . e  d writeFull2(table,id,field,sid,stamp,fschema("Type"),select(spos,"Format"))
 ;
 ; StaticScalar
 ;
 s stamp=$$getStamp1^dcDb(table,id,field) i stamp="" w ScalarNull quit
 ;
 s spid=$s($d(fschema("ForeignKey")):^Record(table,id,field,stamp,"Data",0),1:"")
 i (spid'="")&$$writeFullForeign(fschema("ForeignKey"),spid,when,.select,spos,lvl,historical) quit
 ;
 w StartRec
 w:$d(^Record(table,id,field,stamp,"Tags")) Field_"Tags"_ScalarStr_^Record(table,id,field,stamp,"Tags")
 w Field_"Data"_$$getTypeFor(fschema("Type"))
 ;
 f  s pos=$o(^Record(table,id,field,stamp,"Data",pos)) q:pos=""  d
 . w $$format(table,field,^Record(table,id,field,stamp,"Data",pos),select(spos,"Format"))
 ;
 w EndRec
 ;
 quit
 ;
 ; return true if the value is consumed (written or ignored)
writeFullForeign(table,id,when,select,spos,lvl,historical) n subquery
 q:'$$isCurrent^dcDb(table,id,when,historical) 1
 ;
 i (lvl>1)&$d(select(spos,"Select")) d  quit 1
 . m subquery=select(spos,"Select")
 . d writeRec(table,id,when,.subquery,lvl-1,0,0,historical) 
 ;
 i $d(select(spos,"ForeignField")) d  quit 1
 . s subquery(0,"Field")=select(spos,"ForeignField")
 . s subquery(0,"Format")=select(spos,"Format")
 . d writeFullFld(table,id,when,.subquery,0,1,historical) 
 ;
 quit 0
 ;
 ;
writeFull2(table,id,field,sid,stamp,type,format) n pos
 w StartRec
 w Field_"Sid"_ScalarStr_sid
 w:$d(^Record(table,id,field,sid,stamp,"Tags")) Field_"Tags"_ScalarStr_^Record(table,id,field,sid,stamp,"Tags")
 w:$d(^Record(table,id,field,sid,stamp,"To")) Field_"To"_ScalarBigDateTime_^Record(table,id,field,sid,stamp,"To")
 w:$d(^Record(table,id,field,sid,stamp,"From")) Field_"From"_ScalarBigDateTime_^Record(table,id,field,sid,stamp,"From")
 w Field_"Data"_$$getTypeFor(type)
 ;
 f  s pos=$o(^Record(table,id,field,sid,stamp,"Data",pos)) q:pos=""  d
 . w $$format(table,field,^Record(table,id,field,sid,stamp,"Data",pos),format)
 ;
 w EndRec
 quit
 ;
 ;
 ;
format(table,field,val,format) quit:format="Tr" $$tr^dcStrUtil("_enum_"_table_"_"_field_"_"_val)
 ; TODO support date and number formatting, maybe str padding
 quit val
 ;
 ;
 ;
getTypeFor(type) quit:type="Time" ScalarTime
 quit:type="Date" ScalarDate
 quit:type="DateTime" ScalarDateTime
 quit:type="Id" ScalarId
 quit:type="Integer" ScalarInt 
 quit:type="Json" ScalarJson
 quit:type="Decimal" ScalarDec
 quit:type="BigInteger" ScalarBigInt
 quit:type="BigDecimal" ScalarBigDec
 quit:type="Number" ScalarNum
 quit:type="Boolean" ScalarBool
 quit:type="Binary" ScalarBin
 quit:type="BigDateTime" ScalarBigDateTime
 quit ScalarStr
 ;
 ;
 ; Request
 ;
 ; d local^dcConn("QUERY dcSelectDirect")
 ;
 ;  Table			name
 ;	When			"" = now
 ;					[stamp] = on or before this time
 ;
 ;  Where
 ;			"Name"				expression name: "And", "Or", "Equal", etc.  -or-  field name  -or-  "Filter"
 ;			"A"					value to compare against, or param to Filter
 ;				"Field"			if from database
 ;				"Format"
 ;				"Value"			if literal
 ;				"Composer"		composer script to generate content
 ;			"B"					value to compare against, or param to Filter
 ;			"C"					value to compare against, or param to Filter
 ;			"Children"
 ;					0,"Name"
 ;					1...
 ;
 ;  Select	list of fields to query
 ;			0,
 ;				"Field"			name
 ;				"Format"		format of field if scalar
 ;				"Name"			display name of field
 ;				"ForeignField"	value field in fk relationship
 ;				"Table"			for reverse foreign
 ;				"Composer"		composer script to generate content
 ;				"Select"		list of fields to query, see above
 ;						0,
 ;						1...
 ;			1...
 ;
 ;  Collector
 ;			"Name"				code to execute to get collection
 ;			"Values"				
 ;					0 			value to match
 ;					1...
 ;			"From"				value to start at, inclusive
 ;			"To"				value to end at, exclusive
 ;			"Field"				if not using Code to get collection, use a Field instead
 ;
 ;	Historical	true / false
 ;
 ; Result
 ;		List of records, content based on Select
 ;
select n id,table,when,select,where,collector,cstate,cname,historical
 s table=Params("Table"),when=Params("When"),historical=Params("Historical")
 i (table="") d err^dcConn(50013) quit
 s:when="" when=$$whenNow^dcTimeUtil()
 ;
 s:$d(Params("Select"))=0 Params("Select")="*"
 ;
 m select=Params("Select")
 m where=Params("Where")
 m collector=Params("Collector")
 ;
 i select="*" d  
 . s select(0,"Field")="Id",select(0,"Name")="Id"
 . f i=1:1  s fname=$o(^Schema(table,"Fields",fname)) q:fname=""  d
 . . s select(i,"Field")=fname,select(i,"Name")=fname
 ;
 i collector("Name")'="" d  quit
 . s cname=collector("Name")		; "cstate" is a variable available to the collector for state across calls
 . i ^Collector(cname)="" q
 . w StartList
 . f  x "s id=$$"_^Collector(cname)_"()" q:id=""  d  q:Errors
 . . d:$$check^dcDbSelect(table,id,when,.where,historical) writeRec(table,id,when,.select,5,1,0,historical)
 . w EndList
 ;
 i collector("Field")'="" d  quit
 . i $d(collector("Values")) d  q
 . . n values m values=collector("Values")
 . . w StartList
 . . f  s id=$$loopValues^dcDb(table,collector("Field"),.values,.cstate,when,historical) q:id=""  d
 . . . d:$$check^dcDbSelect(table,id,when,.where,historical) writeRec(table,id,when,.select,5,1,0,historical)
 . . w EndList
 . ;
 . w StartList
 . f  s id=$$loopRange^dcDb(table,collector("Field"),collector("From"),collector("To"),.cstate,when,historical) q:id=""  d 
 . . d:$$check^dcDbSelect(table,id,when,.where,historical) writeRec(table,id,when,.select,5,1,0,historical)
 . w EndList
 ;
 w StartList
 ;
 f  s id=$o(^Record(table,id)) q:id=""  d  q:Errors
 . d:$$check^dcDbSelect(table,id,when,.where,historical) writeRec(table,id,when,.select,5,1,0,historical)
 ;
 w EndList
 ;
 quit
 ;
 ;
 ; Request
 ;
 ; d local^dcConn("QUERY dcListDirect")
 ;
 ;  Table			name
 ;	When			"" = now
 ;					[stamp] = on or before this time
 ;
 ;  Where
 ;			"Name"				expression name: "And", "Or", "Equal", etc.  -or-  field name  -or-  "Filter"
 ;			"A"					value to compare against, or param to Filter
 ;				"Field"			if from database
 ;				"Format"
 ;				"Value"			if literal
 ;				"Composer"		composer script to generate content
 ;			"B"					value to compare against, or param to Filter
 ;			"C"					value to compare against, or param to Filter
 ;			"Children"
 ;					0,"Name"
 ;					1...
 ;
 ;  Select	field to query
 ;			"Field"			name
 ;			"Format"		format of field if scalar
 ;			"ForeignField"	value field in fk relationship
 ;			"Composer"		composer script to generate content
 ;			"Table"			for reverse foreign
 ;			"Select"		list of fields to query, see above
 ;					0,
 ;					1...
 ;
 ;  Collector
 ;			"Name"				code to execute to get collection
 ;			"Values"				
 ;					0 			value to match
 ;					1...
 ;			"From"				value to start at, inclusive
 ;			"To"				value to end at, exclusive
 ;			"Field"				if not using Code to get collection, use a Field instead
 ;
 ;	Historical	true / false
 ;
 ; Result
 ;		List of values, content based on Select
 ;
list n id,table,when,select,where,collector,cstate,cname,historical
 s table=Params("Table"),when=Params("When"),historical=Params("Historical")
 i (table="") d err^dcConn(50013) quit
 s:when="" when=$$whenNow^dcTimeUtil()
 ;
 s:$d(Params("Select"))=0 Params("Select","Field")="Id"
 ;
 m select(0)=Params("Select")
 m where=Params("Where")
 m collector=Params("Collector")
 ;
 i collector("Name")'="" d  quit
 . s cname=collector("Name")		; "cstate" is a variable available to the collector for state across calls
 . i ^Collector(cname)="" q
 . w StartList
 . f  x "s id=$$"_^Collector(cname)_"()" q:id=""  d  q:Errors
 . . d:$$check^dcDbSelect(table,id,when,.where,historical) writeCompactFld(table,id,when,.select,0,5,historical)
 . w EndList
 ;
 i collector("Field")'="" d  quit
 . i $d(collector("Values")) d  q
 . . n values m values=collector("Values")
 . . w StartList
 . . f  s id=$$loopValues^dcDb(table,collector("Field"),.values,.cstate,when,historical) q:id=""  d
 . . . d:$$check^dcDbSelect(table,id,when,.where,historical) writeCompactFld(table,id,when,.select,0,5,historical)
 . . w EndList
 . ;
 . w StartList
 . f  s id=$$loopRange^dcDb(table,collector("Field"),collector("From"),collector("To"),.cstate,when,historical) q:id=""  d 
 . . d:$$check^dcDbSelect(table,id,when,.where,historical) writeCompactFld(table,id,when,.select,0,5,historical)
 . w EndList
 ;
 w StartList
 ;
 f  s id=$o(^Record(table,id)) q:id=""  d  q:Errors
 . d:$$check^dcDbSelect(table,id,when,.where,historical) writeCompactFld(table,id,when,.select,0,5,historical)
 ;
 w EndList
 ;
 quit
 ;
 ;
 ;