Skip to content

Instantly share code, notes, and snippets.

@pavei
Created March 17, 2021 17:59
Show Gist options
  • Save pavei/a725ae790593bca8c5cca6be169732e2 to your computer and use it in GitHub Desktop.
Save pavei/a725ae790593bca8c5cca6be169732e2 to your computer and use it in GitHub Desktop.
const dbTiscoski = require('../config/database_tiscoski');
exports.listVerbasEDatas = async (req, res) => {
try {
var result = await this.listVerbasSemData();
res.status(200).send({ result });
}
catch (error) {
console.error(error);
res.status(502).send({ message: `${error}` });
}
}
exports.listVerbasSemData = async function listVerbasSemData() {
var resultList = [];
const query = "SELECT " +
"Seq = v.seq_verba_fabr_lc, Criacao = CONVERT(Date, dt_criacao), " +
"Fabric = cd_fabric, " +
"Tipo = CASE WHEN v.cd_verba_fabr_tp_lanc = 'DUBO' THEN 'BONIF' WHEN v.cd_verba_fabr_tp_lanc = 'DCTR' THEN 'CONTR' ELSE 'VERBA' END," +
"Lancamento = t.descricao, " +
"Valor = REPLACE(REPLACE(CONVERT(VARCHAR, valor, 1), ',', ''), '.', ','), " +
"ND = ISNULL(nu_tit, ''), NF = ISNULL(nu_nf_compra, ''), " +
"Descricao = ISNULL(referencia, '')," +
"DtRef = ISNULL(d.dt_referencia,''), " +
"Usuario = v.cd_usuario " +
" FROM " +
"MOINHO.dbo.verba_fabr_lc v " +
"INNER JOIN MOINHO.dbo.verba_fabr_tp_lanc t ON v.cd_verba_fabr_tp_lanc = t.cd_verba_fabr_tp_lanc " +
"LEFT JOIN TISCOSKI.dbo.data_verba d ON v.seq_verba_fabr_lc = d.seq_verba_fabr_lc " +
"where " +
"(dt_criacao >= '2020-01-01' OR v.seq_verba_fabr_lc IN (2093469, 2095710, 2095881))" +
"AND v.seq_verba_fabr_lc NOT IN(2141812, 2141410, 2136433, 2136038, 2136037, 2115123, 2115120, " +
"2115119, 2115118, 2115117, 2112531, 2112527, 2106762, 2103413, 2103412, 2107210, 2122859, 2124009, 2136966)" +
"AND v.cd_verba_fabr_tp_lanc IN('ASNN', 'AJSD', 'C', 'RCNN', 'NFCB', 'CRCN', 'DCTR', 'DUBO') " +
"AND situacao = 'VL' AND dt_referencia IS NULL";
await dbTiscoski.raw(query)
.catch(console.error)
.then(data => {
for (let index in data) resultList.push(data[index]);
});
return resultList;
}
exports.putUpdateVerbaSemData = async function (req, res) {
try {
var precos = await this.put_insertDataVerba(req.params.id);
console.log(req.params);
if (!precos) throw Error('Problemas ao realizar a alteração da data da verba.');
else res.status(200).send('Data atualizada com sucesso!');
}
catch (error) {
console.error(error);
res.status(502).send({ message: error });
}
}
exports.put_insertDataVerba = async function (id) {
await dbTiscoski.into('data_verba').insert({seq_verba_fabr_lc: id, data_verba : new Date()}).then().catch(error => { throw error; });
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment