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:processUUID := request:get-parameter('subProcessUUID','');
declare variable $local:communityId := request:get-parameter('communityId','');
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: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:getDbName($communityId as xs:string, $connection as xs:long) {
let $query := "SELECT `databaseName` FROM `mobile1_8`.`community_hubs` WHERE `communityId` = '" || $communityId || "' "
let $run := server:executeSQL($query, $connection)
return $run//sql:databaseName/text()
};
declare function local:updateSpStatusForProcess($processDoc as item ()*, $spStatus as xs:string, $message as xs:string, $id as xs:string, $instanceInfo as item()*) {
try
{
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($spStatus eq "error")
then(
map {
"isError": "true",
"message": $message
}
)
else(
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": $err:description
}
}
};
let $connection := server:mysql_1-8_connection()
let $processUUID := $local:processUUID,
$communityId := $local:communityId
let $processDoc1 := local:getDoc($processUUID, $communityId)
return
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 * { () }
let $run :=
try {
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 $log := util:log("warn","debug sqlReportServiceGenerateQuery step 001") :)
let $doc := $doc1?result
let $meta-data := $doc?meta-data
let $data := $doc?model?pending?data?*
let $seq := $doc?model?pending?seq
let $execute :=
for $row in $data?sqlModelRepeat?sqlModeldef?ModelData?*
let $id := $row?taxonomy?code
let $config := local:getConfig($id)?result
let $query := sqlQueryGenerator:main($config, $communityId, $row?taxonomy?label, $connection)
return server:executeSQL($query, $connection)
return
if(data($execute/@isError) = "false")
then(
let $fileName := "sqlPublishedViews_1_Community_" || $communityId
let $json :=
map {
"_id": $fileName,
"data" : map {
"items": map {
"item": array {
for $row in $data?sqlModelRepeat?sqlModeldef?ModelData?*
return map {
"code": $row?taxonomy?code,
"label": map {
"en": $row?taxonomy?label
}
}
}
}
}
}
let $upsert := parse-json(couch-gateway:upsert-to-couchDb($server:sync-taxonomy-bucket, $fileName, format:map2json($json)))
return
if($upsert?ok)
then(
map {
"isError": "false",
"message": "success",
"upsert": $upsert
}
)
else(
map {
"isError": "true",
"message": "Failed to upsert doc with id = " || $fileName || " | reason = " || $upsert?reason,
"upsert": $upsert
}
)
)
else(
map {
"isError": "true",
"message": data($execute/@errorMessage)
}
)
)
}
catch * {
map {
"isError": "true",
"message": $err:description
}
}
return
if($run?isError eq "false")
then(
let $spStatus := "success"
return local:updateSpStatusForProcess($processDoc, $spStatus, $run?message, "SDOReportSql", $run?upsert)
)
else(
let $spStatus := "error"
return local:updateSpStatusForProcess($processDoc, $spStatus, $run?message, "", map {})
)
)