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:strToSQLField($string as xs:string){
"`" || $string || "`"
};
declare function local:sqlTypeConversion($type as xs:string) {
switch ($type)
case "varchar"
return "string"
case "bigint"
return "integer"
case "int"
return "integer"
case "timestamp"
return "dateTime"
case "tinyInt"
return "boolean"
case "dec"
return "decimal"
default
return $type
};
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", $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 $processUUID := $local:processUUID,
$communityId := $local:communityId,
$connection := server:mysql_1-8_connection()
let $processDoc1 := local:getDoc($processUUID, $communityId)
return
if($processDoc1?isError)
then(
map {
"isError": "true"
}
)
else (
let $processDoc := $processDoc1?result
return
let $indicators := try{$processDoc?indicators?*} catch * { () }
let $run :=
try {
for $indicator in $indicators
where $indicator?id eq 'SDOReportSql'
let $uuid := $indicator?instances?*?uuid,
$title := $indicator?instances?*?title
let $doc1 := local:getDoc($uuid, $communityId)
return
if($doc1?isError)
then(
map {
"isError": "true",
"message": "Failed to fetch doc with uuid = " || $uuid || " and communityId = " || $communityId
}
)
else (
let $doc := $doc1?result
let $id := $doc?_id
let $model := $doc?model
let $pending := $model?pending
let $pendingData := $pending?data
let $SDOReportSql := $pendingData?SDOReportSql
let $view := $SDOReportSql?View?code
let $viewConfig1 := local:getConfig($view)
return
if($viewConfig1?isError)
then(
map {
"isError": "true",
"message": "Failed fetch config with uuid = " || $view
}
)
else(
let $viewConfig := $viewConfig1?result
let $select := $viewConfig?select
let $dataTypes := sqlQueryGenerator:getDataTypes($communityId, $SDOReportSql?View?label, $connection)
let $json :=
array {
for $field at $seq in $select?*
return
map {
"selectField": $field?header,
"columnHeader": $field?header,
"columnWidth": map {
"code": "10",
"label": "10"
},
"dataType" : local:sqlTypeConversion($dataTypes[?field eq $field?header]?dataType),
"displayOrder": $seq,
"reference": $field?field
}
}
let $selectRepeat :=
map {
"selectDef" : map {
"RepeatData": $json
},
"summary": "",
"header": ""
}
let $SDOReportSql := map:remove($SDOReportSql, "selectRepeat")
let $SDOReportSql := map:put($SDOReportSql, "selectRepeat", $selectRepeat)
let $pendingData := map:remove($pendingData, "SDOReportSql")
let $pendingData := map:put($pendingData, "SDOReportSql", $SDOReportSql)
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 $doc := map:remove($doc, "model")
let $doc := map:put($doc, "model", $model)
let $upsert := parse-json(couch-gateway:upsert-to-couchDb($server:sync-bucket||"_" || $communityId, $id, format:map2json($doc)))
return
if($upsert?ok)
then(
map {
"isError": "false",
"message": "success",
"upsert": $upsert
}
)
else(
map {
"isError": "true",
"message": "Failed to upsert doc with id = " || $id || " | reason = " || $upsert?reason
}
)
)
)
}
catch * {
map {
"isError": "false",
"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 {})
)
)