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','SDOSqlModels');
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:cleanFieldCode($field as xs:string) {
let $tokenized := tokenize($field, "-")
let $cleaned := replace($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:getBasepath($viewId as xs:string) {
let $viewDoc1 := local:getConfig($viewId)
return
if($viewDoc1?isError)
then(
""
)
else (
let $viewDoc := $viewDoc1?result
let $debug := util:log("warn","debug local:getBasepath $viewDoc "|| format:map2jsonNoIndent($viewDoc))
return
for $view in $viewDoc?view?*
let $debug := util:log("warn","debug local:getBasepath $view "|| format:map2jsonNoIndent($view))
let $basepath := $view?basepath
let $debug := util:log("warn","debug local:getBasepath $basepath "|| format:map2jsonNoIndent($basepath))
return
if(string-length($basepath) > 0)
then($basepath || ".\[\].")
else("")
)
};
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: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(empty($globals?*))then($viewId)else(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", ".latitude")
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:strToSQLField($string as xs:string){
"`" || $string || "`"
};
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(
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:createSQLModelConfig($doc as item()*, $seq as xs:integer){
let $configId := $doc?modelId || ":" || $seq
return
map {
"_id": $configId,
"version": $seq,
"published": false(),
"title": $doc?modelName,
"views":
array {
for $row at $seq in $doc?viewsRepeat?viewsDef?RepeatData?*
let $view := $row?view
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
return
map {
"viewId": $view?code,
"viewAlias": if($isSubquery)then($viewDoc?alias)else("view" || format-number($seq, '000')),
"isSubquery": $isSubquery,
"context": $view?viewContext,
"seq": $seq
}
)
},
"joins": if($doc?createJoin eq 'Yes')
then (
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": local:cleanFieldCode($row?LeftField?code),
"isLeftSubquery": $isLeftViewServerGenerated,
"type": $row?joinType?code,
"rightViewId": $row?RightView?code,
"isRightSubquery": $isRightViewServerGenerated,
"rightField": local:cleanFieldCode($row?RightField?code),
"seq": $seq
}
}
)
else([]),
"select":
let $globalData := if($doc?createJoin eq 'No')then([])else($doc?globalsRepeat?globalsDef?globalsData)
let $select :=
for $view at $viewSeq in $doc?viewsRepeat?viewsDef?RepeatData?*
let $code := $view?view?code,
$label := $view?view?label,
$selectAll := $view?viewSelectAll eq 'Yes'
let $isSubquery :=
if([contains($code, 'SQLReportJoins'),contains($code, 'SDOSqlSingleObjectViews'),contains($code, 'SDOsqlDataObjectViewUnion')] = true())
then(true())
else(false())
return
if($selectAll)
then(
let $viewDoc1 := local:getConfig($code)
return
if($viewDoc1?isError)
then(
map {}
)
else (
let $viewDoc := $viewDoc1?result
return
if($isSubquery)
then(
for $row in $viewDoc?select?*
return
if($row?field = ["_id", "_communityId", "_profileId", "_subprofileId", "_seq"])
then(
let $globalView := if($globalData[?masterView?code eq $code]?code = $row?field)then($code)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": $code,
"header": $row?header,
"label": $row?label,
"agregationDo": $row?agregationDo,
"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(), $code, $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(), $code, $viewSeq, $globalData, $basepath)
)
)
)
)
)
else(
let $fieldsForView := $doc?SelectRepeat?SelectRepeatDef?SelectRepeatData?*[?View?code eq $code]
let $isSubquery :=
if([contains($code, 'SQLReportJoins'),contains($code, 'SDOSqlSingleObjectViews'),contains($code, 'SDOsqlDataObjectViewUnion')] = true())
then(true())
else(false())
let $basepath := if($isSubquery)then("")else(local:getBasepath($code))
return
if(empty($fieldsForView))
then()
else(
for $field in $fieldsForView
let $fieldId := if(string-length($basepath) > 0)then(replace($field?SelectField?code, $basepath, ""))else($field?SelectField?code)
let $header := replace(local:getValidSQLElement($field?SelectField?code), "`", "")
return
map {
"viewId": $code,
"header": $header,
"label": $field?selectAlias,
"agregationDo": $field?selectAggregation?code,
"field": $field?SelectField?code,
"type": if(contains($field?SelectField?label, ["- code", "- label"]))then("search")else(""),
"viewSeq": $viewSeq
}
)
)
let $ignoreDuplicates :=
for $map at $seq in $select
where count($select[?header eq $map?header]?viewId) > 1
return
if($globalData[?code eq "duplicates"]?masterView?code eq $map?viewId)
then()
else($seq)
return
array {
for $row at $seq in $select
where not($seq = $ignoreDuplicates)
return
map {
"viewId": $row?viewId,
"header": $row?header,
"label": $row?label,
"agregationDo": $row?agregationDo,
"field": $row?field,
"type": $row?type,
"seq": $seq,
"viewSeq": $row?viewSeq
}
},
"where": array {
if($doc?isWhereClause eq "No")
then()
else(
for $row at $seq in $doc?whereRepeat?whereDef?whereRepeatData?*
let $isSubquery :=
if([contains($row?whereView?code, 'SQLReportJoins'),contains($row?whereView?code, 'SDOSqlSingleObjectViews'),contains($row?whereView?code, 'SDOsqlDataObjectViewUnion')] = true())
then(true())
else(false())
let $basepath := if($isSubquery)then("")else(local:getBasepath($row?whereView?code))
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": $row?whereView?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 $isSubquery :=
if([contains($row?groupView?code, 'SQLReportJoins'),contains($row?groupView?code, 'SDOSqlSingleObjectViews'),contains($row?groupView?code, 'SDOsqlDataObjectViewUnion')] = true())
then(true())
else(false())
let $basepath := if($isSubquery)then("")else(local:getBasepath($row?groupView?code))
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": $row?groupView?code,
"seq": $seq
}
)
}
}
};
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 $modelConfig := local:createSQLModelConfig($data, $seq)
let $coreViews :=
for $view in $modelConfig?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 $coreViews := webhook2:map2array(if(array:size($modelConfig?views) > 1)then(local:removeDuplicateViews($coreViews))else($coreViews))
let $queryString := try{sqlQueryGenerator:generateModelQuery($modelConfig)} catch * {$err:description}
let $model := local:updateDataModelWithQuery($uuid, $queryString, $local:setId)
let $modelConfig := map:put($modelConfig, "coreViews", $coreViews)
let $modelConfig := map:put($modelConfig, "query", $queryString)
return
map {
"config": $modelConfig,
"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)
}
)