查询外部数据源

TIP

查询外部数据源数据

---

查询外部数据源

//查询源数据SQL
let sqlText = `select bf.id                                              as _id,
bf.owner_name,
bf.dept_name,
bf.seller_name,
bf.seller_code,
bf.customer_code,
bf.customer_name,
budget_type,
CAST(COALESCE(value_summary, 0) as DECIMAL(18, 2)) as value_summary,
year,
audit_status,
locked_status,
concat(bf.seller_code, bf.seller_name, bf.customer_code, bf.customer_name, bf.dept_code,
      bf.dept_name)                                  search_key
from dw_budget_fill bf
where bill_type = 'FORECAST_FILL'
and budget_type in ('SAL_N_T', 'SAL_N_R', 'SAL_N_R_C')

`
//定义查询条件
const NAME_MAP = {
   searchKey: 'search_key',
   seller_code: 'seller_code'
}
//定义查询条件类型
const TYPE_MAP = {
   searchKey: 'text',
   seller_code: 'text'

}
//分页查询
let pageSize = _requestBody.page ? _requestBody.page.pageSize || 10 : 10
let pageIndex = _requestBody.page ? _requestBody.page.pageIndex || 1 : 1
if (pageSize > 1000 && pageSize < 1) {
   pageSize = 10
}
if (pageIndex < 1) {
   pageIndex = 1
}
//定义参数，转化拼接查询条件sql
const sqlParam = []
let queryParam = _requestBody.properties || []
queryParam.forEach(q => {
   if (!q || !q.name || !q.op || !('value' in q) || q.value === null || q.value === '') {
       return
   }
   if (!(q.name in NAME_MAP)) {
       return
   }
   const dbName = NAME_MAP[q.name]
   const cType = TYPE_MAP[q.name]
   if (cType === 'text') {
       if (q.op === '=') {
           sqlText += ` and ${dbName} = '${q.value}'`
       } else if (q.op === 'like') {
           sqlText += ` and ${dbName} like '%${q.value}%'`
       } else if (q.op === 'in') {
           sqlText += ` and ${dbName} in (${q.value.split(',').map(s => "'" + s + "'").join(',')})`

       }
   } else if (cType === 'date') {
       sqlText += ` and ${dbName} ${q.op} to_date ('${q.value}','YYYY-MM-DD HH24:MI:SS')`
   }
})
//拼接sql排序
sqlText += `  order by owner_code,dept_code,seller_code,customer_code,order_serial asc`
//定义返回内容
let httpRes = {
   code: 0,
   message: 'success',
   data: {
       count: 0,
       list: [],
       footer: []
   }
}
//定义postgres数据库连接
const _i = await api.database.getPostgresInstance('RfhbbHQpnQRnXWnG')
try {
   //查询源数据
   const totalNum = await _i.count(sqlText, sqlParam)
   const res = await _i.queryPage(sqlText, sqlParam, pageIndex - 1, pageSize)
   const snt = res.filter(s => s.budget_type === 'SAL_N_T')
   //返回内容，合计计算
   httpRes = {
       code: 0,
       message: 'success',
       data: {
           count: totalNum,
           list: res,
           footer: [{
               __name: '合计',
               value_summary: snt.reduce((p, n) => p + Number(n.value_summary), 0).toFixed(2)
           }]
       }
   }
   //HTTP请求返回
   api.http.setHttpResponse(httpRes)

}
catch (e) {
   api.logger.info(e)
}
finally {
   await _i.close()
   //mogodb数据不需要关闭，其他数据库需要关闭连接
}