Ola, tenho um duvida em criar um dataset com select e um union de 3 tabelas, bom o processo é uma consulta que pesquisa nf no banco de dados do fluig,
SELECT obra, nf, fornecedor,data_vencimento,data_pagamento,data_emissao,data_vencimento,null
FROM ml001178
UNION
SELECT obra, nf, fornecedor,data_vencimento,data_pagamento,null,data_vencimento,oc
FROM ml001119
union all
SELECT obra, nf, fornecedor,data_vencimento,data_pagamento,data_emissao,data_vencimento,oc
FROM ml001284
union all
select obra,nf,fornecedor,null,null,data_emissao,null,null
from ml001197
bom segui o codigo do meu dataset, creio que estou errando na query
var init = {
datasetName: 'SelectNotaFiscaldeServico',
fluigService: 'FluigDSRO',
primaryKey: [
'nf',
'ID'
],
columns: [
// 'NumeroSolicitacao',
'ID',
'obra',
'nf',
'data_emissao',
'data_vencimento',
'data_pagamento',
'oc',
// 'companyid',
]
}
function defineStructure() {
for (var currentColumn = 0; currentColumn < init.columns.length; currentColumn++) {
addColumn(init.columns[currentColumn], DatasetFieldType.STRING)
}
setKey(init.primaryKey)
}
function createStructure() {
var dataset = DatasetBuilder.newDataset()
for (var index = 0; index < init.columns.length; index++) {
dataset.addColumn(init.columns[index])
}
return dataset
}
function createErrorStructure() {
var dataset = DatasetBuilder.newDataset()
dataset.addColumn('error')
return dataset
}
function onSync(lastSyncDate) {
var dataset = createStructure()
var query = createDataset()
if (!query.values) {
return query
}
var primaryKeyCodes = []
for (var currentRow = 0; currentRow < query.values.length; currentRow++) {
var primaryKey = ''
for (var index = 0; index < init.primaryKey.length; index++) {
primaryKey += query.getValue(currentRow, init.primaryKey[index])
}
primaryKeyCodes[primaryKey] = true
var row = new Array()
for (var currentColumn = 0; currentColumn < init.columns.length; currentColumn++) {
var value = query.getValue(currentRow, init.columns[currentColumn])
row.push((value && value.trim() !== '') ? value : '')
}
dataset.addOrUpdateRow(row)
}
/**
* Getting current rows from API
*/
query = DatasetFactory.getDataset(init.datasetName, null, null, null)
if (query && query.values) {
for (var currentRow = 0; currentRow < query.values.length; currentRow++) {
var primaryKey = ''
for(var index = 0; index < init.primaryKey.length; index++) {
primaryKey += query.getValue(currentRow, init.primaryKey[index])
}
if (primaryKeyCodes[primaryKey] === undefined) {
var row = new Array()
for (var currentColumn = 0; currentColumn < init.columns.length; currentColumn++) {
row.push(query.getValue(currentRow, init.columns[currentColumn]))
}
dataset.deleteRow(row)
}
}
}
return dataset
}
function onMobileSync(user) {
var result = {
'fields': init.columns,
'constraints': new Array(),
'sortFields': new Array()
}
return result
}
function createDataset(fields, constraints, sortFields) {
var dataset = createStructure()
try {
var initialContext = new javax.naming.InitialContext()
var dataSource = initialContext.lookup('/jdbc/' + init.fluigService)
var obra = ''
var fornecedor = ''
var dataEmissao = ''
var dataVencimento = ''
var dataPagamento = ''
var notafiscal = ''
var numeroOC = ''
var filial = ''
if (constraints) {
for (var index in constraints) {
if (constraints[index].getFieldName().toLowerCase() == 'Obra'.toLowerCase()) {
obra = constraints[index].getInitialValue()
}
else if (constraints[index].getFieldName().toLowerCase() == 'fornecedor'.toLowerCase()) {
fornecedor = constraints[index].getInitialValue()
}
else if (constraints[index].getFieldName().toLowerCase() == 'DataEmissao'.toLowerCase()) {
dataEmissao = constraints[index].getInitialValue()
}
else if (constraints[index].getFieldName().toLowerCase() == 'DataVencimento'.toLowerCase()) {
dataVencimento = constraints[index].getInitialValue()
}
else if (constraints[index].getFieldName().toLowerCase() == 'DataPagamento'.toLowerCase()) {
dataPagamento = constraints[index].getInitialValue()
}
else if (constraints[index].getFieldName().toLowerCase() == 'NotaFiscal'.toLowerCase()) {
notafiscal = constraints[index].getInitialValue()
}
else if (constraints[index].getFieldName().toLowerCase() == 'NumeroOC'.toLowerCase()) {
numeroOC = constraints[index].getInitialValue()
}
else if (constraints[index].getFieldName().toLowerCase() == 'filial'.toLowerCase()) {
filial = constraints[index].getInitialValue()
}
}
}
var query =
+ "SELECT obra, "
+ "nf, "
+ "fornecedor, "
+ "data_vencimento, "
+ "data_pagamento, "
+ "null, "
+"data_vencimento, "
+ "oc, "
+ "from ml001119"
+ "UNION all"
+ "SELECT obra,"
+"nf,"
+"fornecedor,"
+"data_vencimento,"
+"data_pagamento,"
+"data_emissao,"
+"data_vencimento,"
+"oc,"
+"from ml001284 "
+"UNION all"
+"SELECT obra,"
+"nf,"
+"fornecedor,"
+"null,"
+"null,"
+"data_emissao,"
+"null,"
+"null"
+"FROM ml001197"
var connection = dataSource.getConnection()
var statement = connection.createStatement()
var resultSet = statement.executeQuery(query)
while (resultSet.next()) {
var row = new Array()
for (var currentColumn = 0; currentColumn < init.columns.length; currentColumn++) {
var value = resultSet.getObject(init.columns[currentColumn])
row.push((value != null) ? value.toString().trim() : 'null')
}
dataset.addRow(row)
}
}
catch (exception) {
var row = new Array()
row.push('Error to execute dataset "' + init.datasetName + '": ' + exception.message)
dataset = createErrorStructure()
dataset.addRow(row)
log.info('Error to execute dataset "' + init.datasetName + '": ' + exception.message)
}
finally {
if (statement != null) {
statement.close()
}
if (connection != null) {
connection.close()
}
}
return dataset
}