xquery version "3.1";
import module namespace sqlQueryGenerator="sqlQueryGenerator.xqm" at "xmldb:exist:///db/kwantu-resource/webhookServices/customAPIServices/Reports/sqlQueryGenerator.xqm";
import module namespace server="mod-server" at "xmldb:exist:///db/kwantu-resource/lib/mod-server.xql";
import module namespace format="format" at "xmldb:exist:///db/kwantu-resource/collectorServices/format.xqm";
import module namespace webhook2="webhook2" at "xmldb:exist:///db/kwantu-resource/modules/webhook2.xqm";
import module namespace couch-gateway = "http://www.kwantu.net/api/couchbase/couch-gateway.xqm" at "xmldb:exist:///db/kwantu-resource/api/couchbase/couch-gateway.xqm";
declare variable $local:communityId := request:get-parameter('communityId','');
declare variable $local:processUUID := request:get-parameter('subProcessUUID','');
declare variable $local:setId := request:get-parameter('setId', '');
declare variable $ms_1-8_connection := server:mysql_1-8_connection();
declare variable $local:maxElementLength := 100;
declare function local:getDoc($docId as xs:string, $communityId as xs:string ){
let $useCouchDb := $server:sync-bucket||"_" || $communityId
let $inputDoc1:= couch-gateway:fetchCouchbaseDoc($docId,$useCouchDb)
return $inputDoc1
};
declare function local:getConfig($docId as xs:string){
let $useCouchDb := $server:sync-config-bucket
let $inputDoc1:= couch-gateway:fetchCouchbaseDoc($docId,$useCouchDb)
return $inputDoc1
};
declare function local:getTaxonomy($docId as xs:string){
let $useCouchDb := $server:sync-taxonomy-bucket
let $inputDoc1:= couch-gateway:fetchCouchbaseDoc($docId,$useCouchDb)
return $inputDoc1
};
declare function local:getCategoryVersion($viewId as xs:string) {
let $config := local:getConfig($viewId)
return $config?result?view?*?categoryVersion
};
declare function local:removeDuplicateViews($views as item()*){
let $uuids := distinct-values($views?uuid)
return
for-each($uuids, function($u) {map { "uuid": $u, "version": distinct-values($views[?uuid eq $u]?version)}})
};
declare function local:getBasepath($viewId as xs:string) {
let $viewDoc1 := local:getConfig($viewId)
return
if($viewDoc1?isError)
then(
""
)
else (
let $viewDoc := $viewDoc1?result
return
for $view in $viewDoc?view?*
let $basepath := $view?basepath
return
if(string-length($basepath) > 0)
then($basepath || ".\[\].")
else("")
)
};
declare function local:cleanFieldCode($field as xs:string) {
let $tokenized := tokenize($field, "-")
let $cleaned := $tokenized[last()]
return
if(contains($cleaned, ["label", "code"]))
then(
if(contains($cleaned, "label"))
then(replace($cleaned, "label", ".label"))
else(replace($cleaned,"code",".code"))
)
else($cleaned)
};
declare function local:getSelectMap($field as item()*, $isRepeat as xs:boolean, $viewId as xs:string, $viewSeq as xs:integer, $globals as item()*, $basepath as xs:string){
let $fieldId := if($isRepeat)then(replace($field?code, $basepath, ""))else($field?code)
return
if($fieldId = ["_title", "_setId", "_applicationId", "_status", "_workflowValidDate", "_rowId"] or contains($fieldId, ['.fileData', '.mime', '.isChanged', '.fileName', '.geoData', '.type']))
then()
else(
if($fieldId = ["_id", "_communityId", "_profileId", "_subprofileId", "_seq"])
then(
let $code := if($isRepeat and $fieldId eq "_seq")then($fieldId || "Key")else($fieldId)
let $globalView := if($globals[?masterView?code eq $viewId]?code = $code)then($viewId)else("")
let $code := replace(local:getValidSQLElement($code), "`", "")
return
if(string-length($globalView) eq 0)
then()
else(
map {
"viewId": $globalView,
"header": $code,
"label": $field?label?en,
"agregationDo": "None",
"field": $fieldId,
"type": "",
"viewSeq": $viewSeq
}
)
)
else(
if(contains($fieldId, ".gps"))
then(
let $latField := replace($fieldId, ".gps", ".latitude"),
$lngField := replace($fieldId, ".gps", ".longitude")
return
(
map {
"viewId": $viewId,
"header": replace(local:getValidSQLElement($latField), "`", ""),
"label": replace($field?label?en, "gps", "latitude"),
"agregationDo": "None",
"field": $latField,
"type": if(contains($fieldId, [".label", ".code"]))then("search")else(""),
"viewSeq": $viewSeq
} ,
map {
"viewId": $viewId,
"header": replace(local:getValidSQLElement($lngField), "`", ""),
"label": replace($field?label?en, "gps", "longitude"),
"agregationDo": "None",
"field": $lngField,
"type": if(contains($fieldId, [".label", ".code"]))then("search")else(""),
"viewSeq": $viewSeq
}
)
)
else(
let $fieldId :=
if(contains($fieldId, ".lat"))
then(replace($fieldId, ".lat", ".latitude"))
else(
if(contains($fieldId, ".lng"))
then(replace($fieldId, ".lng", ".longitude"))
else($fieldId)
)
return
map {
"viewId": $viewId,
"header": replace(local:getValidSQLElement($fieldId), "`", ""),
"label": $field?label?en,
"agregationDo": "None",
"field": $fieldId,
"type": if(contains($fieldId, [".label", ".code"]))then("search")else(""),
"viewSeq": $viewSeq
}
)
)
)
};
declare function local:getValidSQLElement($string as xs:string) {
switch ($string)
case "_profileId"
return "`G_PROFILEUUID`"
case "_subprofileId"
return "`G_SUBPROFILEUUID`"
case "_communityId"
return "`G_COMMUNITYID`"
case "_id"
return "`G_UUID`"
case "_applicationId"
return "`G_APPID`"
case "_seq"
return "`G_SEQ`"
case "_seqKey"
return "`seqKey`"
default
return
if(contains($string, "."))
then(
let $split := tokenize($string, "[.]")
return
switch ($split[2])
case "code"
return
upper-case("`" || substring($split[1],1,$sqlQueryGenerator:maxElementLength) || "_CODE`")
case "label"
return
upper-case("`" || substring($split[1],1,$sqlQueryGenerator:maxElementLength) || "`")
case "uuid"
return
upper-case("`" || substring($split[1],1,$sqlQueryGenerator:maxElementLength) || "_UUID`")
case "latitude"
return
upper-case("`" || substring(substring-before($string, ".latitude"),1,$sqlQueryGenerator:maxElementLength) || "_LATITUDE`")
case "longitude"
return
upper-case("`" || substring(substring-before($string, ".longitude"),1,$sqlQueryGenerator:maxElementLength) || "_LONGITUDE`")
case "timestamp"
return
upper-case("`" || substring(substring-before($string, ".timestamp"),1,$sqlQueryGenerator:maxElementLength) || "_TIMESTAMP`")
case ""
return
upper-case("`" || substring($string,1,$sqlQueryGenerator:maxElementLength) || "`")
default
return
upper-case("`" || substring($split[1],1,$sqlQueryGenerator:maxElementLength) || "_" || $split[2] || "`")
)
else(upper-case("`" || substring($string,1,$sqlQueryGenerator:maxElementLength) || "`"))
};
declare function local:updateDataModelWithQuery($indicatorId as xs:string, $query as xs:string, $setId as xs:string) {
let $doc := local:getDoc($indicatorId, $local:communityId)?result
let $model := $doc?model
let $pending := $model?pending
let $pendingData := $pending?data?*
let $pendingData := map:remove($pendingData, "sampleQuery")
let $pendingData := map:put($pendingData, "sampleQuery", $query)
let $pendingData := map { $setId : $pendingData }
let $pending := map:remove($pending, "data")
let $pending := map:put($pending, "data", $pendingData)
let $model := map:remove($model, "pending")
let $model := map:put($model, "pending", $pending)
let $entry := map:remove($doc, "model")
let $entry := map:put($entry, "model", $model)
return parse-json(couch-gateway:upsert-to-couchDb($server:sync-bucket || "_" || $local:communityId, $indicatorId, format:map2json($entry)))
};
declare function local:updateSpStatusForProcess($processDoc as item ()*, $spStatus as xs:string, $instanceInfo as item()*) {
try
{
let $id := $local:setId
let $entry := $processDoc
let $entry := map:remove($entry, "spStatus")
let $entry := map:put($entry, "spStatus", $spStatus)
let $entry :=
if($spStatus eq "error")
then(
$entry
)
else(
let $indicators := $entry?indicators
let $indicators :=
for $indicator in $indicators?*
return
if($indicator?id eq $id)
then(
map {
"id": $id,
"instances": array {
for $instance in $indicator?instances?*
return
if($instance?uuid eq $instanceInfo?id)
then(
map {
"rev": $instanceInfo?rev,
"title": $instance?title,
"uuid": $instanceInfo?id,
"seq": $instance?seq,
"key": $instance?key
}
)
else(
$instance
)
}
}
)
else(
$indicator
)
let $newEntry := map:remove($entry, "indicators")
return map:put($newEntry, "indicators", webhook2:map2array($indicators))
)
let $upsert := parse-json(couch-gateway:upsert-to-couchDb($server:sync-bucket || "_" || $local:communityId, $local:processUUID, format:map2json($entry)))
return
if($upsert?ok)
then(
if($spStatus eq "error")
then(
map {
"isError": "true",
"message": "failed to create subquery"
}
)
else(
map {
"isError": "false",
"message": "success"
}
)
)
else(
map {
"isError": "true",
"message": "Failed to update spStatus in process document with processId=" || $local:processUUID || " spStatus=" || $spStatus
}
)
} catch * {
map {
"isError": "true",
"message": "Failed to update spStatus in process document with processId=" || $local:processUUID || " spStatus=" || $spStatus
}
}
};
declare function local:createSubqueryConfig($doc as item()*, $viewId as xs:string, $viewType as xs:string){
let $configId := $viewId
return
switch ($viewType)
case 'SQLReportJoins'
return
let $leftViews := array{$doc?JoinRepeat?JoinRepeatDef?JoinRepeatData?*?leftView?code}
let $rightViews := array{$doc?JoinRepeat?JoinRepeatDef?JoinRepeatData?*?RightView?code}
let $views := webhook2:map2array(array:flatten(($leftViews, $rightViews)))
let $views := distinct-values($views)
return
map {
"_id": $configId,
"title": $doc?name,
"alias": $doc?viewAlias,
"type": $viewType,
"views": array {
for $view at $seq in $views
let $isSubquery :=
if([contains($view, 'SQLReportJoins'),contains($view, 'SDOSqlSingleObjectViews'),contains($view, 'SDOsqlDataObjectViewUnion')] = true())
then(true())
else(false())
let $viewDoc1 := local:getConfig($view)
return
if($viewDoc1?isError)
then(
map {}
)
else (
let $viewDoc := $viewDoc1?result
return
map {
"viewId": $view,
"viewAlias": if($isSubquery)then($viewDoc?alias)else("view" || format-number($seq, '000')),
"isSubquery": $isSubquery,
"setId": $viewType,
"seq": $seq
}
)
},
"globals": array {
for $row in $doc?globalsRepeat?globalsDef?globalsData?*
return
map {
"globalField": $row?code,
"view": $row?masterView?code
}
},
"joins": array {
for $row at $seq in $doc?JoinRepeat?JoinRepeatDef?JoinRepeatData?*
let $isLeftViewServerGenerated :=
if([contains($row?leftView?code, 'SQLReportJoins'),contains($row?leftView?code, 'SDOSqlSingleObjectViews'),contains($row?leftView?code, 'SDOsqlDataObjectViewUnion')] = true())
then(true())
else(false())
let $isRightViewServerGenerated :=
if([contains($row?RightView?code, 'SQLReportJoins'),contains($row?RightView?code, 'SDOSqlSingleObjectViews'),contains($row?RightView?code, 'SDOsqlDataObjectViewUnion')] = true())
then(true())
else(false())
return
map {
"leftViewId": $row?leftView?code,
"leftField": if($isRightViewServerGenerated)then(local:cleanFieldCode($row?LeftField?code))else($row?RightField?code),
"isLeftSubquery": $isLeftViewServerGenerated,
"type": $row?joinType?code,
"rightViewId": $row?RightView?code,
"rightField": if($isRightViewServerGenerated)then(local:cleanFieldCode($row?RightField?code))else($row?RightField?code),
"isRightSubquery": $isRightViewServerGenerated,
"seq": $seq
}},
"select":
let $globalData := $doc?globalsRepeat?globalsDef?globalsData?*
let $selectMaps :=
array{
for $view at $viewSeq in $views
let $uuid := $view
let $isSubquery :=
if([contains($view, 'SQLReportJoins'),contains($view, 'SDOSqlSingleObjectViews'),contains($view, 'SDOsqlDataObjectViewUnion')] = true())
then(true())
else(false())
let $viewDoc1 := local:getConfig($view)
return
if($viewDoc1?isError)
then(
map {}
)
else (
let $viewDoc := $viewDoc1?result
return
if($isSubquery)
then(
for $row in $viewDoc?select?*
return
if($row?header = ["G_UUID", "G_COMMUNITYID", "G_PROFILEID", "G_SUBPROFILEID", "G_SEQ", "seqKey"])
then(
let $globalView := if($globalData[?masterView?code eq $uuid]?code = $row?field)then($uuid)else("")
let $header := replace(local:getValidSQLElement($row?field), "`", "")
return
if(string-length($globalView) eq 0)
then()
else(
map {
"viewId": $globalView,
"header": $header,
"label": $row?label,
"agregationDo": "None",
"field": $row?field,
"type": "",
"viewSeq": $viewSeq
}
)
)
else(
map {
"viewId": $uuid,
"header": $row?header,
"label": $row?label,
"agregationDo": "None",
"field": $row?header,
"type": $row?type,
"viewSeq": $viewSeq
}
)
)
else(
for $view2 in $viewDoc?view?*
let $setId := $view2?categoryId,
$version := $view2?categoryVersion,
$basepath := $view2?basepath
return
if(string-length($basepath) > 0)
then(
let $repeatTaxonomy := "Field_Repeat_" || $setId || ":" || $version || ":" || replace($basepath, "[.]", ":")
let $repeatTaxonomyDoc1 := local:getTaxonomy($repeatTaxonomy)
return
if($repeatTaxonomyDoc1?isError)
then(
map {}
)
else(
let $repeatTaxonomyDoc := $repeatTaxonomyDoc1?result
return
for $row in $repeatTaxonomyDoc?data?items?item?*
return local:getSelectMap($row, true(), $uuid, $viewSeq, $globalData, $basepath || ".\[\].")
)
)
else(
let $taxonomy := "Field_Indicator_" || $setId || ":" || $version
let $taxonomyDoc1 := local:getTaxonomy($taxonomy)
return
if($taxonomyDoc1?isError)
then(
map {}
)
else (
let $taxonomyDoc := $taxonomyDoc1?result
return
for $row in $taxonomyDoc?data?items?item?*
return local:getSelectMap($row, false(), $uuid, $viewSeq, $globalData, $basepath)
)
)
)
)
}
let $ignoreDuplicates :=
for $map at $seq in $selectMaps?*
where count($selectMaps?*[?header eq $map?header]?viewId) > 1
return
if($globalData[?code eq "duplicates"]?masterView?code eq $map?viewId)
then()
else(
map {
"seq": $seq,
"field": $map?header,
"count": count($selectMaps?*[?header eq $map?header]?viewId)
}
)
return
array {
for $map at $seq in $selectMaps?*
where not($seq = $ignoreDuplicates?seq)
return
map {
"viewId": $map?viewId,
"header": $map?header,
"label": $map?label,
"agregationDo": $map?agregationDo,
"field": $map?field,
"type": $map?type,
"seq": $seq,
"viewSeq": $map?viewSeq
}
}
}
case 'SDOsqlDataObjectViewUnion'
return
map {
"_id": $configId,
"title": $doc?name,
"type": $viewType,
"alias": $doc?viewAlias,
"views": array{
for $row at $seq in $doc?selectViewForRepeat?selectViewForRepeatDef?selectViewForRepeatData?*
let $view := $row?viewSelect
let $isSubquery :=
if([contains($view?code, 'SQLReportJoins'),contains($view?code, 'SDOSqlSingleObjectViews'),contains($view?code, 'SDOsqlDataObjectViewUnion')] = true())
then(true())
else(false())
let $viewDoc1 := local:getConfig($view?code)
return
if($viewDoc1?isError)
then(
map {}
)
else (
let $viewDoc := $viewDoc1?result
let $viewAlias := if($isSubquery)then($viewDoc?viewAlias)else("view:" || $seq)
return
map {
"viewId": $view?code,
"viewAlias": $viewAlias,
"isSubquery": $isSubquery,
"setId": $viewType,
"seq": $seq
}
)
},
"select":
let $globalData := $doc?globalsRepeat?globalsDef?globalsData?*
let $selectMaps :=
array{
for $view at $viewSeq in $doc?selectViewForRepeat?selectViewForRepeatDef?selectViewForRepeatData?*?viewSelect
where $viewSeq eq 1
let $uuid := $view?code
let $isSubquery :=
if([contains($view?code, 'SQLReportJoins'),contains($view?code, 'SDOSqlSingleObjectViews'),contains($view?code, 'SDOsqlDataObjectViewUnion')] = true())
then(true())
else(false())
let $viewDoc1 := local:getConfig($uuid)
return
if($viewDoc1?isError)
then(
map {}
)
else (
let $viewDoc := $viewDoc1?result
return
if($isSubquery)
then(
for $row at $seq in $viewDoc?select?*
let $header := replace(local:getValidSQLElement($row?field), "`", "")
return
map {
"viewId": $uuid,
"header": $header,
"agregationDo": $row?agregationDo,
"field": $row?field,
"type": $row?type,
"seq": $seq,
"viewSeq": $viewSeq
}
)
else(
for $view2 in $viewDoc?view?*
let $setId := $view2?categoryId,
$version := $view2?categoryVersion,
$basepath := $view2?basepath
return
if(string-length($basepath) > 0)
then(
let $repeatTaxonomy := "Field_Repeat_" || $setId || ":" || $version || ":" || replace($basepath, "[.]", ":")
let $repeatTaxonomyDoc1 := local:getTaxonomy($repeatTaxonomy)
return
if($repeatTaxonomyDoc1?isError)
then(
map {}
)
else(
let $repeatTaxonomyDoc := $repeatTaxonomyDoc1?result
return
for $row in $repeatTaxonomyDoc?data?items?item?*
return local:getSelectMap($row, true(), $uuid, $viewSeq, $globalData, $basepath || ".\[\].")
)
)
else(
let $taxonomy := "Field_Indicator_" || $setId || ":" || $version
let $taxonomyDoc1 := local:getTaxonomy($taxonomy)
return
if($taxonomyDoc1?isError)
then(
map {}
)
else (
let $taxonomyDoc := $taxonomyDoc1?result
return
for $row in $taxonomyDoc?data?items?item?*
return local:getSelectMap($row, false(), $uuid, $viewSeq, $globalData, $basepath)
)
)
)
)
}
return
array {
for $map at $seq in $selectMaps?*
return
map {
"viewId": $map?viewId,
"header": $map?header,
"label": $map?label,
"agregationDo": $map?agregationDo,
"field": $map?field,
"type": $map?type,
"seq": $seq,
"viewSeq": $map?viewSeq
}
}
}
case 'SDOSqlSingleObjectViews'
return
let $uuid := $doc?view?code
let $isSubquery :=
if([contains($uuid, 'SQLReportJoins'),contains($uuid, 'SDOSqlSingleObjectViews'),contains($uuid, 'SDOsqlDataObjectViewUnion')] = true())
then(true())
else(false())
let $basepath := if($isSubquery)then("")else(local:getBasepath($doc?view?code))
return
map {
"_id": $configId,
"title": $doc?name,
"viewAlias": $doc?viewAlias,
"type": $viewType,
"views": array{
let $view := $doc?view
let $isSubquery :=
if([contains($view?code, 'SQLReportJoins'),contains($view?code, 'SDOSqlSingleObjectViews'),contains($view?code, 'SDOsqlDataObjectViewUnion')] = true())
then(true())
else(false())
return
map {
"viewId": $view?code,
"viewAlias": replace($doc?name, '[^A-Za-z_0-9]', "-") || ":1",
"isSubquery": $isSubquery,
"setId": $viewType
}},
"where": array {
if($doc?isWhereClause eq "No")
then()
else(
for $row at $seq in $doc?whereRepeat?whereDef?whereRepeatData?*
let $fieldId :=
if($isSubquery)
then($row?whereField?alias)
else(if(string-length($basepath) > 0)then(replace($row?whereField?code, $basepath, ""))else($row?whereField?code))
return
map {
"field": $fieldId,
"viewId": $doc?view?code,
"operator": $row?whereOperator?code,
"value": $row?whereValue,
"seq": $seq
}
)
},
"group": array {
if($doc?isGroupBy eq "No")
then()
else(
for $row at $seq in $doc?groupRepeat?groupRepeatDef?groupRepeatData?*
let $fieldId :=
if($isSubquery)
then($row?groupField?alias)
else(if(string-length($basepath) > 0)then(replace($row?groupField?code, $basepath, ""))else($row?groupField?code))
return
map {
"field": $fieldId,
"viewId": $doc?view?code,
"seq": $seq
}
)
},
"select":
let $view := $doc?view
let $globalData := $doc?globalsRepeat?globalsDef?globalsData?*
let $selectMaps :=
array {
if($doc?selectAll eq 'No')
then(
if($isSubquery)
then(
for $row at $seq in $doc?SelectRepeat?SelectDef?SelectRepeatData?*
let $fieldId := local:cleanFieldCode($row?SelectField?label)
let $header := replace(local:getValidSQLElement($fieldId), "`", "")
return
map {
"viewId": $uuid,
"header": if($row?selectAlias eq '')then($header)else($row?selectAlias),
"label": if($row?selectLabel eq '')then($header)else($row?selectLabel),
"agregationDo": $row?selectAggregation?code,
"field": $row?SelectField?label,
"type": if(contains($row?SelectField?label, ["- code", "- label"]))then("search")else(""),
"seq": $seq,
"viewSeq": 1
}
)
else(
for $row at $seq in $doc?SelectRepeat?SelectDef?SelectRepeatData?*
let $fieldId := if(string-length($basepath) > 0)then(replace($row?SelectField?code, $basepath, ""))else($row?SelectField?code)
let $header := replace(local:getValidSQLElement($fieldId), "`", "")
return
map {
"viewId": $uuid,
"header": if($row?selectAlias eq '')then($header)else($row?selectAlias),
"label": if($row?selectLabel eq '')then($header)else($row?selectLabel),
"agregationDo": $row?selectAggregation?code,
"field": $fieldId,
"type": if(contains($row?SelectField?label, ["- code", "- label"]))then("search")else(""),
"seq": $seq,
"viewSeq": 1
}
)
)
else (
let $viewDoc1 := local:getConfig($uuid)
return
if($viewDoc1?isError)
then(
map {}
)
else (
let $viewDoc := $viewDoc1?result
return
if($isSubquery)
then(
for $row at $seq in $viewDoc?select?*
let $fieldId := local:cleanFieldCode($row?SelectField?label)
let $header := replace(local:getValidSQLElement($fieldId), "`", "")
return
map {
"viewId": $uuid,
"header": $header,
"label": $header,
"agregationDo": $row?agregationDo,
"field": $fieldId,
"type": $row?type,
"seq": $seq,
"viewSeq": 1
}
)
else(
for $view2 in $viewDoc?view?*
let $setId := $view2?categoryId,
$version := $view2?categoryVersion,
$basepath := $view2?basepath
return
if(string-length($basepath) > 0)
then(
let $repeatTaxonomy := "Field_Repeat_" || $setId || ":" || $version || ":" || replace($basepath, "[.]", ":")
let $repeatTaxonomyDoc1 := local:getTaxonomy($repeatTaxonomy)
return
if($repeatTaxonomyDoc1?isError)
then(
map {}
)
else(
let $repeatTaxonomyDoc := $repeatTaxonomyDoc1?result
return
for $row in $repeatTaxonomyDoc?data?items?item?*
return local:getSelectMap($row, true(), $uuid, 1, $globalData, $basepath || ".\[\].")
)
)
else(
let $taxonomy := "Field_Indicator_" || $setId || ":" || $version
let $taxonomyDoc1 := local:getTaxonomy($taxonomy)
return
if($taxonomyDoc1?isError)
then(
map {}
)
else (
let $taxonomyDoc := $taxonomyDoc1?result
return
for $row in $taxonomyDoc?data?items?item?*
return local:getSelectMap($row, false(), $uuid, 1, $globalData, $basepath)
)
)
)
)
)
}
return
array {
for $map at $seq in $selectMaps?*
return
map {
"viewId": $map?viewId,
"header": $map?header,
"label": $map?label,
"agregationDo": $map?agregationDo,
"field": $map?field,
"type": $map?type,
"seq": $seq,
"viewSeq": $map?viewSeq
}
}
}
default
return ''
};
let $processUUID := $local:processUUID,
$communityId := $local:communityId
let $processDoc1 := local:getDoc($processUUID, $communityId)
let $doc :=
if($processDoc1?isError)
then(<status isError="true" code="updateSqlModel" message="Input document with communityId {$communityId} and processUUID {$processUUID} not available"/>)
else (
let $processDoc := $processDoc1?result
let $indicators := try{$processDoc?indicators?*} catch * { () }
return
for $indicator in $indicators
let $uuid := $indicator?instances?*?uuid,
$title := $indicator?instances?*?title
let $doc1 := local:getDoc($uuid, $communityId)
return
if($doc1?isError)
then(<status isError="true" code="updateSqlModel" message="Input document with communityId {$communityId} and uuid {$uuid} not available"/>)
else (
let $doc := $doc1?result
let $meta-data := $doc?meta-data
let $data := $doc?model?pending?data?*
let $seq := $doc?model?pending?seq
let $viewCode := replace($doc?name, " ", "-"),
$viewName := $doc?name
let $viewId := $doc?_id
let $subqueryConfig := local:createSubqueryConfig($data, $viewId, $local:setId)
let $coreViews :=
for $view in $subqueryConfig?views?*
return
if($view?isSubquery)
then(
let $configDoc := local:getConfig($view?viewId)?result
return $configDoc?coreViews
)
else(
map {
"uuid": $view?viewId,
"version": local:getCategoryVersion($view?viewId),
"context": $view?viewContext?code
}
)
let $coreViews := array:flatten($coreViews)
let $queryString := try{sqlQueryGenerator:generateSubquery($subqueryConfig, $local:setId)} catch * {$err:description}
let $coreViews := if(array:size($subqueryConfig?views) > 1)then(local:removeDuplicateViews($coreViews))else($coreViews)
let $coreViews := webhook2:map2array($coreViews)
let $model := local:updateDataModelWithQuery($uuid, $queryString, $local:setId)
let $subqueryConfig := map:put($subqueryConfig, "coreViews", $coreViews)
let $subqueryConfig := map:put($subqueryConfig, "query", $queryString)
return
map {
"config": $subqueryConfig,
"model": $model
}
)
)
return
if($processDoc1?isError)
then(
map {
"isError": "false",
"message": "Error fetching process doc from couch with following parameters: communityId=" || $local:communityId || " processUUID=" || $local:processUUID
}
)
else (
let $processDoc := $processDoc1?result
let $config := $doc?config
return
try {
let $upsert := parse-json(couch-gateway:upsert-to-couchDb($server:sync-config-bucket, $config?_id, format:map2json($config)))
return
if($upsert?ok)
then(
let $spStatus := "success"
return local:updateSpStatusForProcess($processDoc, $spStatus, $doc?model)
)
else(
let $spStatus := "error"
return local:updateSpStatusForProcess($processDoc, $spStatus, $doc?model)
)
} catch * {
let $spStatus := "error"
return local:updateSpStatusForProcess($processDoc, $spStatus, $doc?model)
}
)