local ss = analiz.libs.serv
local log = analiz.libs.logserv
log.init()
SetSecLevel()
ss.s("abort_flag", "1")

local pwd = ss.CreatePwdServ()
local tick = CreateTimeServ()

local ll = analiz.libs.logs
local finn_serv = analiz.mix.finnova.serv
local xml_io = CreateXmlIO()
local mem = ss.CreateMemItem(8000)

local orgclient_default = pwd:get("PLIB").org

local org_engine = CreateOrgClientEngine()
if OrgClientEngine_errors() ~= "" then
    ss.l(" failed load orgclients:" .. OrgClientEngine_errors())
    ss.l("sleep&restart finas")
    ss.e("SLEEP 3 min")
    ss.a("1")
end

function go_pskill()
    ss.l(debug.traceback())
    ss.l("go_pskill")
    ss.e("calc_param pskill_exe $(_ANALIZ_BIN)\\pskill.exe")
    ss.e("GET_SHORT_PATH pskill_exe $(pskill_exe)")
    ss.e("GET_CURRENT_PROCESS_ID txm_pid")
    ss.e("echo execute $(pskill_exe) $(txm_pid)")
    ss.e("execute $(pskill_exe) $(txm_pid)")
    ss.e("_abort 1")
end

local fgenerator = CreateGenerateThrow()
fgenerator:init("finas")

list_ver_id = GetCurrentListID({pwd_plib = "PLIB"})
ss.l("list_ver_id:" .. list_ver_id)

-- local clog =  ll.CreateCompactLog2({limit=80,limit_dt=600,separator=""})

local uniq_id = ss.b("$(service_family):$(UNIQUE_IDENTIFIER)")
rstatus = CreateRecordStatusObject({name = uniq_id, lazy = true})

-- move to failedServ.lua
-- last_corrid=nil
-- last_bid=nil
-- last_cache=nil

internal_state = nil

-- move to failedServ.lua
-- global_failedQueue=nil
-- global_con=nil
-- global_inf=nil

function AddInternalState(msg)
    -- ss.l("InternalState:"..msg)
    internal_state = msg
end
-- -------------------------------------------------------------------------

function GetScriptTable()
    analiz:set("tmp_plib", "Sanction lists\\Std sanction list")
    analiz:execute(
        "EXTRACT_FROM_PLIB_TABLENAME tmp PLIB $(tmp_plib) $(empty) $(empty)")
    analiz:execute("echo plib table:$(tmp)")
    --   analiz:execute("abort 1")
    return analiz:get("tmp")

end

local function send_(inf, enq, b1, data, corrid, jheader, prior_arg,
                     expiration_arg)
    b1:write(data)

    local o = {}

    o.BYTES_LOB = b1
    finn_serv.send_simple_ex(inf, enq, o, {BYTES_LOB = "blob"}, {
        correlation = corrid,
        header = jheader,
        prior = prior_arg,
        expiration = expiration_arg
    })
end

-- -------------------------init
ss.l("queue.engine init...")
local engine = CreateQueueEngine()
engine:init()

local input_queue_name = global_oracle_setting.internal.finas_queue
ss.l("create reader:" .. input_queue_name)
local reader = engine:createReader({queue = input_queue_name})

ss.l("writer create..")
local writer = engine:createWriter({
    name = "final",
    mode = "writer2",
    queue = global_oracle_setting.finnova.out
})
local writer_post = engine:createWriter({
    name = "final",
    mode = "writer2",
    queue = global_oracle_setting.internal.post_queue
})

ss.l("f_ailed.writer setting..")
global_failedQueue = writer
global_engine = engine
init_failed_info()

local function add_item(nc_queue_prop, name_arg, val_arg)
    nc_queue_prop[#nc_queue_prop + 1] = {name = name_arg, val = val_arg}
end

local function CreateServCD(data_ex)
    ss.l("CreateServCD.start")

    local cd = {}

    cd.data_ = data_ex

    -- ss.tprint(data_ex)

    function cd:add_to_header(nc_queue_prop)

        --     local function add_cd_(name)    if self.data_[name]=="1" then jmsheader:set_string_property(name,"1") end     end
        local function add_cd_(name)
            local v = self.data_[name]
            if v == nil or v == "" then v = "N" end
            add_item(nc_queue_prop, name, v)
        end

        add_cd_("CF_KD_PEP_CD")
        add_cd_("CF_SANCTIONS_CD")
        add_cd_("CF_INTERNAL_CD")
        add_cd_("CF_CRIME_CD")
    end

    ss.l("CreateServCD.end")
    return cd
end

-- -------------------------------------------------------------------------
local function setting_header_to_finnova(nc_queue_prop, bid, score, corrid,
                                         data_ex, current_message)
    --[[    local jmsheader=header

    jmsheader:clear()

    jmsheader:set_type("JmsBytesMessage")
    jmsheader:set_appid("finasService")
    jmsheader:set_protocol(0)
    jmsheader:set_groupid('lex')
    jmsheader:set_groupseq(1)
]]

    local prospero_type = current_message.PROSPERO_TYPE
    if prospero_type == "nc_kundenstamm" or
        finn_serv.IsKundenstamm(current_message) then
        add_item(nc_queue_prop, 'CF_CORRELATION_ID', bid);
        add_item(nc_queue_prop, "F_RECORD_TYPE", "KD02")
    else
        add_item(nc_queue_prop, 'CF_PAYMENT_ID', bid);
    end

    add_item(nc_queue_prop, "MSG_UUID", corrid);

    local hit_flag = ""

    local CF_HIT_FLAG = ""

    if data_ex.lasterror_full ~= "" then
        CF_HIT_FLAG = "2"
    else
        local is_hit = score > finn_serv.score_threshold
        if is_hit then
            CF_HIT_FLAG = "1"
        else
            CF_HIT_FLAG = "0"
        end
    end

    local HIT_DESCRIPTION = ""
    if data_ex ~= nil and data_ex.list_id ~= "" then
        HIT_DESCRIPTION = data_ex.first .. " " .. data_ex.last .. ";" ..
                              data_ex.country .. ";" .. data_ex.dob .. ";" ..
                              data_ex.list .. ";ID=" .. data_ex.list_id
    end

    add_item(nc_queue_prop, "CF_HIT_FLAG", CF_HIT_FLAG)
    add_item(nc_queue_prop, "CF_HIT_DESCRIPTION", HIT_DESCRIPTION)
    add_item(nc_queue_prop, "CF_RESP_MESSAGE", data_ex.lasterror_full)
    add_item(nc_queue_prop, "CF_RESP_SCORE", "" .. score)

    do
        local cd = CreateServCD(data_ex)
        cd:add_to_header(nc_queue_prop)
    end

    local cache_i = {}
    function a_(name)
        if cache_i[name] ~= nil then return end
        cache_i[name] = 1
        local cur = current_message[name]
        if cur == nil then return end
        add_item(nc_queue_prop, name, cur)
    end

    a_("F_BANK_ID")
    a_("F_CUSTOMER_ID")
    a_("F_PARTNER_CODE")
    a_("PROCESS_CODE")
    a_("F_PROCESS_CODE")
    a_("F_PAYMENT_SYSTEM")
    a_("F_PAYMENT_TYPE")
    a_("F_CS_MSG_LNR")

    function a_list(name)
        local root = global_oracle_setting.fields.interface
        if root == nil then return end
        local f = root[name]
        if f == nil then return end
        for i = 1, #f do a_(f[i]) end
    end
    a_list("kundenstamm")
    a_list("checkpayment")

    if false then
        local fields = global_oracle_setting.fields.namechecking_transit
        for i = 1, #fields do
            local ff = fields[i]
            local vv = current_message[ff]
            if vv ~= nil then
                add_item(nc_queue_prop, ff,
                         ss.def(vv, ff .. " not found.failed No 977"))
            else
                add_item(nc_queue_prop, ff, "not found(finasMQ)")
            end
        end
    end

    ss.l("setting_header_to_finnova.bid:" .. bid .. ",corrid:" ..
             ss.def(corrid, "<NULL>") .. ",CF_HIT_FLAG:" .. CF_HIT_FLAG ..
             ",HIT_DESCRIPTION:" .. HIT_DESCRIPTION .. ",lasterro_r:" ..
             data_ex.lasterror_full)
end

local function setting_header_to_post(nc_queue_prop, bid, score, corrid,
                                      data_ex, current_message)
    --[[    local jmsheader=header

    jmsheader:clear()

    jmsheader:set_type("JmsBytesMessage")
    jmsheader:set_appid("finasService")
    jmsheader:set_protocol(0)
    jmsheader:set_groupid('lex')
    jmsheader:set_groupseq(1)
]]
    add_item(nc_queue_prop, 'BID', bid);
    add_item(nc_queue_prop, "MSG_UUID", corrid);

    local hit_flag = ""

    local CF_HIT_FLAG = ""

    if data_ex.lasterror_full ~= "" then
        CF_HIT_FLAG = "2"
    else
        local is_hit = score > finn_serv.score_threshold
        if is_hit then
            CF_HIT_FLAG = "1"
        else
            CF_HIT_FLAG = "0"
        end
    end

    local HIT_DESCRIPTION = ""
    if data_ex ~= nil and data_ex.list_id ~= "" then
        HIT_DESCRIPTION = data_ex.first .. " " .. data_ex.last .. ";" ..
                              data_ex.country .. ";" .. data_ex.dob .. ";" ..
                              data_ex.list .. ";ID=" .. data_ex.list_id
    end

    local prospero_type = current_message.PROSPERO_TYPE
    if prospero_type == "nc_kundenstamm" or
        finn_serv.IsKundenstamm(current_message) then
        add_item(nc_queue_prop, "F_RECORD_TYPE", "KD02")
    else
    end

    add_item(nc_queue_prop, "PROSPERO_NC_FLAG", CF_HIT_FLAG)
    add_item(nc_queue_prop, "PROSPERO_NC_SCORE", "" .. score)
    add_item(nc_queue_prop, "PROSPERO_NC_DESCRIPTION", HIT_DESCRIPTION)
    add_item(nc_queue_prop, "PROSPERO_NC_RESP_MESSAGE", data_ex.lasterror_full)

    do
        local cd = CreateServCD(data_ex)
        cd:add_to_header(nc_queue_prop)
    end

    do
        local fields = global_oracle_setting.fields.namechecking_transit
        for i = 1, #fields do
            local ff = fields[i]
            local vv = current_message[ff]
            if vv ~= nil then
                add_item(nc_queue_prop, ff,
                         ss.def(vv, ff .. " not found.failed No 977"))
            else
                add_item(nc_queue_prop, ff, "not found(finasMQ)")
            end
        end
    end

    ss.l("setting_header_to_post.bid:" .. bid .. ",corrid:" ..
             ss.def(corrid, "<NULL>") .. ",CF_HIT_FLAG:" .. CF_HIT_FLAG ..
             ",HIT_DESCRIPTION:" .. HIT_DESCRIPTION .. ",lasterro_r:" ..
             data_ex.lasterror_full)
end

-- -------------------------------------------------------------------------
ss.l("Finas.init..")
local current_list_table = GetScriptTable()
ss.l("current_list_table:" .. current_list_table)
finasServ:init({
    pwdname = "AmlWeb",
    pwdname_plib = "PLIB",
    script_table = current_list_table,
    org_engine = org_engine
})

local function add_err(ret, emessage)
    ret.ok = false
    if ret.emessage == "" then
        ret.emessage = emessage
        return
    end
    ret.emessage = ret.emessage .. ";" .. emessage
end
-- -----------------------------------------------------------
local function toNAMECHECK_kundenstamm(name)
    local op = {lexengine = "finnovaMQ.kundenstamm." .. uniq_id}
    op.org_engine = org_engine
    return finn_serv.toNAMECHECK_kundenstamm(name, op)
end

-- -----------------------------------------------------------
-- -----------------------------------------------------------
-- -----------------------------------------------------------

local function toNAMECHECK(name)
    local op = {}
    op.lexengine = "finnovaMQ." .. uniq_id
    op.org_engine = org_engine
    return finn_serv.toNAMECHECK(name, op)
end

local wait_count = 0

function convert(val, cache)
    local o = {}
    o.bid = val.bid
    o.first = ss.ReplaceValue(val.firstname, "'", "''")
    o.last = ss.ReplaceValue(val.lastname, "'", "''")
    o.add_name1 = ss.ReplaceValue(val.add_name1, "'", "''")
    o.add_name2 = ss.ReplaceValue(val.add_name1, "'", "''")
    o.dob = val.dob
    if o.dob == "<nil>" then o.dob = '' end -- fast fix
    o.nationality = ""
    o.country = val.countries_filter
    o.address = ss.ReplaceValue(val.address, "'", "''")
    o.F_BANK_ID = ss.def(cache.F_BANK_ID, "F_BANK_ID")
    o.PROSPERO_PROFILE_NAME = ss.def(cache.PROSPERO_PROFILE_NAME,
                                     "PROSPERO_PROFILE_NAME")
    return o
end

local function transform_(body)
    local r = ss.ReplaceValue(body, "'", "''")
    return r
end

local function send_xml_to_db_(bid) xml_io:send_to_db(bid) end

seclevel = CreateSecLevel("finas")
local function sec_fun(key, val) return seclevel:get("data", key, val) end

local function send_std_post(ctx, finn_serv, xml_params, xml_params, val,
                             corrid, prior)
    local nc_queue_prop = {}
    local out_xml = finn_serv.GetOutXML(xml_params)
    ss.l("out xml(post):" .. out_xml)

    setting_header_to_finnova(nc_queue_prop, xml_params.bid,
                              analiz:tonumber(xml_params.score),
                              xml_params.corrid, xml_params, val.raw.cache)

    setting_header_to_post(nc_queue_prop, xml_params.bid,
                           analiz:tonumber(xml_params.score), xml_params.corrid,
                           xml_params, val.raw.cache)

    --      local prior=7
    local expiration = 180 * 20 * 20 -- 20 hours

    --      send_(ctx.writer_post,b1,out_xml,corrid,nc_queue_prop,prior,expiration)
    local out_message = "todo"
    ctx.writer_post:send_to_finnova_out(out_message, corrid, prior,
                                        val.raw.cache, out_xml, nc_queue_prop)

    --      engine.con:commit()
    rstatus:add(val.bid, corrid, "send to post", ss.def(xml_params.score, "0"))
    last_corrid = nil
    last_prior = nil

    rstatus:commit({lazy = true, lazyKoef = 5})

    AddInternalState("Wait new records")

end
-- ----------
local function send_std_out(ctx, finn_serv, xml_params, xml_params, val, corrid,prior_arg)
    local nc_queue_prop = {}
    ss.l("send_std_out.start")
    local out_xml = finn_serv.GetOutXML(xml_params)

    if val.raw.cache.PROSPERO_OUTPUT_QUEUE == "NO_OUTPUT" then
        ss.l("send_std_out.detect :NO_OUTPUT")
        ss.l("send_std_out.skip send record ro output record")
        return

    end

    log.l("out xml:" .. out_xml, log.level.DEBUG)

    setting_header_to_finnova(nc_queue_prop, xml_params.bid,
                              analiz:tonumber(xml_params.score),
                              xml_params.corrid, xml_params, val.raw.cache)

    local prior = prior_arg
    local expiration = 180 * 20 * 20 -- 20 hours

    --      send_(ctx.writer,b1,out_xml,corrid,nc_queue_prop,prior,expiration)
    local out_message = "todo"
    ctx.writer:send_to_finnova_out(out_message, corrid, prior, val.raw.cache,
                                   out_xml, nc_queue_prop)

    --   engine.con:commit()
    rstatus:add(val.bid, corrid, "finish", ss.def(xml_params.score, "0"))
    last_corrid = nil
    last_prior = nil
    rstatus:commit({lazy = true, lazyKoef = 5})

    AddInternalState("Wait new records")
end

local function obj_get_(queue_msg)
    local msg = queue_msg;
    if msg:isnil() then return nil, nil, nil end
    --   local obj = msg:getObj();
    local msgid = nil
    ss.l("serv_oracle.lua:msg:getCorrelation")
    local corrid = msg:getCorrelation()
    local prior = msg:getPrior()
    ss.l("serv_oracle.lua:msg:getCorrelation:" .. ss.def(corrid, "<nil>") ..
             ".prior:" .. ss.def(prior, "<nil>"))

    msgid = msg:getId()

    local all_prop = msg:property()
    -- ss.l("________________________________________________")
    --   ss.tprint(all_prop)
    -- ss.a(1)
    local ret = {}

    ret.bid = ss.def(all_prop.cache.BID, "")
    ret.lastname = ss.def(all_prop.cache.LASTNAME, "")
    ret.firstname = ss.def(all_prop.cache.FIRSTNAME, "")
    ret.countries_filter = ss.def(all_prop.cache.COUNTRIES_FILTER, "<nil>")
    ret.dob = ss.def(all_prop.cache.DOB, "<nil>")
    ret.start_dt = analiz:tonumber(ss.def(all_prop.cache.START_DT, 0))
    ret.list_ver_id = ss.def(all_prop.cache.LIST_VER_ID, "")
    ret.add_name1 = ss.def(all_prop.cache.ADD_NAME1, "")
    ret.add_name2 = ss.def(all_prop.cache.ADD_NAME2, "")
    ret.address = ss.def(all_prop.cache.ADDRESS, "Address:nul:2")
    ret.remmitance = ss.def(all_prop.cache.REMITTANCE, "REMITTANCE:nul:2")
    ret.reserve2 = ss.def(all_prop.cache.reserve2, "reserve2:nul:2")
    ret.USERNAME = ss.def(all_prop.cache.USERNAME, "username:nul:2") -- todo
    ret.raw = all_prop
    ss.l("debug point:1729")
    -- LL   ss.tprint(ret)
    --    ret.IDNAME = ret.bid
    --    ret.DOB = ret.dob
    return ret, msgid, corrid, prior
end

-- ----------main loop
function main_(ctx)
    last_corrid = nil
    last_bid = nil
    last_cache = nil
    last_prior = nil
    deadline:DeadLineLazy("wait", deadline.time.WAIT)
    local queue_msg = ctx.reader:read()

    local msg, msgid, corrid, prior = obj_get_(queue_msg)

    if msg == nil then
        wait_count = wait_count + 1
        rstatus:commit({lazy = true})
        return
    end

    ss.l("message.prior:" .. prior)
    AddInternalState("load msg")
    local val = msg

    last_corrid = corrid
    last_bid = val.bid
    last_cache = val.raw.cache
    last_prior = prior
    rstatus:add(val.bid, corrid, "finas.start", "")

    if val.lasterror_full == nil then val.lasterror_full = "" end
    log.l("input.val:", log.level.INFO)
    log.tprint_array(val.raw.cache,
                     {transform = sec_fun, level = log.level.INFO})
    --   ss.tprint(val.raw.cache,0,sec_fun) 
    last_cache = val.raw.cache
    if last_cache.PROSPERO_ASSERT_TEST == "1" then
        rstatus:add(val.bid, corrid, "test assert", "")
        ss.l("test assert:" .. last_cache.PROSPERO_ASSERT_TEST_SERVICE)
        fgenerator:doit(last_cache.PROSPERO_ASSERT_TEST_SERVICE)
        ss.l("test assert:" .. last_cache.PROSPERO_ASSERT_TEST_SERVICE ..
                 ":ignore test")
        last_corrid = nil
        last_bid = nil
        last_prior = nil
        return
    end
    if val.firstname == "{C4BC6514-05DF-4C37-9ED4-78C25FAD2874}" or val.lastname ==
        "{C4BC6514-05DF-4C37-9ED4-78C25FAD2874}" then
        last_corrid = nil
        last_bid = nil
        last_cache = nil
        last_prior = nil
        local new_table = GetScriptTable()
        ss.l("change script table.new_table:" .. new_table)
        AddInternalState("change script table")
        finasServ:ChangeScriptTable(new_table)
        return
    end
    ctx.engine:commit() -- test.commit
    -- insert to namecheck

    --  ss.a("1aa")
    local prospero_type = val.raw.cache.PROSPERO_TYPE
    ss.l("prospero_type:'" .. prospero_type .. "'")

    deadline:DeadLine("namecheck", deadline.time.NAMECHECK)
    local err_msg = nil
    if prospero_type == "nc_kundenstamm" then
        AddInternalState("toNAMECHECK_kundenstamm")
        err_msg = toNAMECHECK_kundenstamm(val)
    else
        AddInternalState("toNAMECHECK")
        err_msg = toNAMECHECK(val)
    end

    local corrid_local = ss.def(corrid, "")
    -- send to pfinas
    local ret = nil
    if err_msg ~= nil then
        ss.l("failed bid:" .. val.bid .. ",corrid:" .. ss.def(corrid, "<nil>"))
        ss.l("errof_values:" .. ss.toJson(val))
        ret = {}
        ret.ok = false
        ret.emessage = err_msg
    else
        ss.l("calculate bid:" .. val.bid .. ",corrid:" ..
                 ss.def(corrid, "<nil>"))
        --    ss.tprint(val)
        log.tprint(val, log.level.DEBUG, {no_log_obj = {raw = "no_log"}})
        if val.raw ~= nil then
            log.tprint(val.raw.cache, log.level.DEBUG, {name = "raw.cache"})
        end

        ss.l("Send to finas...")
        AddInternalState("send to finas")

        rstatus:add(val.bid, corrid, "send finas", "")
        local finn_options = {}

        if prospero_type == "nc_kundenstamm" then
            finn_options.match_name = "MatchName3_Kundenstamm"
            local dob = nil
            if not (val.raw.cache.F_BIRTHDAY == nil or val.raw.cache.F_BIRTHDAY ==
                "") then
                dob = val.raw.cache.F_BIRTHDAY
            else
                dob = val.raw.cache.F_BIRTHYEAR
            end
            if dob == nil then dob = "" end
            if dob == "<nil>" then dob = "" end
            if val.address ~= nil and val.address == 'Address:nul:2' then
                val.address = ""
            end
            if val.countries_filter == "<nil>" then
                val.countries_filter = ""
            end

            val.dob = dob
        end
        local fval = convert(val, val.raw.cache)
        log.l("tt---------------------------------------------", log.level.DEBUG)

        log.tprint(fval, log.level.DEBUG)
        ret = finasServ:send_to_finas(fval, nil, finn_options)

        AddInternalState("Save xml to db")
        ss.l("Save xml to db...")
        send_xml_to_db_(val.bid)
        ss.l("calculate bid:end")
        analiz:log("ctx:post_processing.finas processing...end")

    end

    local ret_code = "1"

    if not (ret.ok) then
        analiz:log("ctx:finas failed:" .. ret.emessage)
        ret_code = "-1"
    end
    AddInternalState("update namecheck WORKING_STATE")
    analiz:set("tsql",
               "update namecheck set WORKING_STATE=" .. ret_code ..
                   " where BUSINESSCASEID='" .. val.bid .. "'")
    ss.l("$(now):tsql.v2:$(tsql)")
    analiz:execute("EXEC_SQL AmlWeb $(tsql)")

    analiz:set("tsql", "UPDATE CHECK_STATE SET FINAS_IN_STATE=1")
    analiz:execute("EXEC_SQL AmlWeb  $(tsql)")
    ss.l("calc max score...")

    local cur_res = analiz:map_get("cur_res")
    cur_res:clear()
    AddInternalState("Get current result")

    local tsql = [[declare @threshold float= ]] .. finn_serv.score_threshold ..
                     [[ 
SELECT top 1 Eff_Threshold as SCORE,
             LIST_ID as LIST_ID,
             FirstName as FIRSTNAME, LASTName as LASTNAME,r.list as LIST,l.Country  AS COUNTRY, 
            l.dob as DOB,
	   staging_namecheck.dbo.gsf_IsListType(BUSINESSCASEID,'SANCTION',@threshold) as  CF_SANCTIONS_CD,
           staging_namecheck.dbo.gsf_IsListType(BUSINESSCASEID,'PEP',@threshold) as  CF_KD_PEP_CD,
           staging_namecheck.dbo.gsf_IsListType(BUSINESSCASEID,'BLACK',@threshold) as  CF_INTERNAL_CD,
           staging_namecheck.dbo.gsf_IsListType(BUSINESSCASEID,'CRIME',@threshold) as  CF_CRIME_CD

            FROM [staging_namecheck].[dbo].[NAMERESULT] r join plib_namecheck.dbo.SANCTION_LISTS_VIEW l on l.id=r.LIST_ID  
where BusinessCaseID=']] .. val.bid .. [['  order by Eff_Threshold desc
]]

    ss.s("tsql", tsql)
    --     ss.e("echo tsql.11=$(tsql)")
    log.l("sql:" .. tsql, log.level.DEBUG)
    analiz:execute(
        "ANALIZ_SAFE_EXECUTE ret tmp_emessage ADD_MAP_FROM_SQL_V2 cur_res AmlWeb $(tsql)")
    do
        local emess = ss.g("tmp_emessage")
        if emess ~= "" then
            ss.l("failed:" .. emess)
            add_err(ret, emess)
        end
    end

    local sql_ =
        "select  Eff_Threshold  as SCORE,LIST_ID as LIST_ID,FirstName as FIRSTNAME, LASTName as LASTNAME,r.list as LIST,l.Country  AS COUNTRY, l.dob as DOB, Upper(type_of_list_internal_category) as CUR_LIST FROM [staging_namecheck].[dbo].[NAMERESULT] r join plib_namecheck.dbo.SANCTION_LISTS_VIEW l on l.id=r.LIST_ID " ..
            "  where BusinessCaseID='" .. val.bid ..
            "' and cast(Eff_Threshold as float)>=" .. finn_serv.score_threshold ..
            "  order by Eff_Threshold desc"

    log.l("sql:" .. sql_, log.level.DEBUG)
    local xml_params = {raws = {}}
    local esql, emess = createSqlEngine({
        pwd = "AmlWeb",
        sql = sql_,
        isUpperHeader = "1"
    })
    if emess ~= "" then
        ss.l("failed:" .. emess)
        add_err(ret, emess)
    end
    local ii = 0
    while esql:Next() do
        local r = esql:Record()
        log.l(ii .. ":" .. ss.toJson(r), log.level.DEBUG)
        xml_params.raws[#xml_params.raws + 1] = r
        ii = ii + 1
    end
    esql:Destroy()
    ss.l("_______")

    xml_params.score = cur_res:get("SCORE")
    if xml_params.score == "" then xml_params.score = "0" end

    xml_params.list_id = cur_res:get("LIST_ID")
    xml_params.first = cur_res:get("FIRSTNAME")
    xml_params.last = cur_res:get("LASTNAME")
    xml_params.list = cur_res:get("LIST")
    xml_params.country = cur_res:get("COUNTRY")
    xml_params.dob = cur_res:get("DOB")
    xml_params.CF_SANCTIONS_CD = cur_res:get("CF_SANCTIONS_CD")
    xml_params.CF_KD_PEP_CD = cur_res:get("CF_KD_PEP_CD")
    xml_params.CF_INTERNAL_CD = cur_res:get("CF_INTERNAL_CD")
    xml_params.CF_CRIME_CD = cur_res:get("CF_CRIME_CD")

    xml_params.lasterror_full = ss.def(ret.emessage, "")

    xml_params.bid = val.bid
    xml_params.corrid = ss.def(corrid, "")

    ss.l("score:" .. xml_params.score .. "\tcorrid:" .. corrid_local)
    ss.l("send to out queue..")

    local output_queue = ss.def(val.raw.cache.PROSPERO_AFTER_NC_QUEUENAME, "")
    AddInternalState("send to out queue:" .. output_queue)
    if (output_queue == "" or output_queue == "std") or ret.ok == false then
        send_std_out(ctx, finn_serv, xml_params, xml_params, val, corrid, prior)
    else
        ss.l("send queue:" .. output_queue)
        send_std_post(ctx, finn_serv, xml_params, xml_params, val, corrid, prior)
    end

end

local ctx = {}
ctx.reader = reader
ctx.engine = engine
ctx.writer = writer
ctx.writer_post = writer_post

-- ----------main loop
local t = tick.now()

ss.l("Start")
while true do
    if analiz:IsStop() then break end
    main_(ctx)
    if tick.to_sec(tick.now() - t) > 10 * 50 then
        ss.l("wait_count:" .. wait_count)
        t = tick.now()
    end

    local err = engine:serv():GetLastError()
    if err ~= "" then
        rstatus:close()
        ss.l("oracle.error:" .. err)
        ss.l("restart after cleep")
        ss.e("sleep sec 15")
        ss.l("abort")
        go_pskill()
        ss.a("1")
    end
end

rstatus:close()
ctx.engine:destroy()

ss.s("abort_flag", "")


