Last active
June 7, 2017 05:42
-
-
Save uriee/4b199061082234a9993ca5312f8f52bd to your computer and use it in GitHub Desktop.
cbt_charts
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
var sql = require('mssql'); | |
var express = require('express'); | |
var app = express(); | |
var bodyParser = require('body-parser'); | |
var cors = require('cors'); | |
var config = { | |
user: '', | |
password: '', | |
server: '', | |
database: '' | |
} | |
app.use(bodyParser.urlencoded({ | |
extended: true | |
})); | |
app.use(bodyParser.json()); | |
var port = process.env.PORT || 4000; | |
var router = express.Router(); | |
app.use(function(req, res, next) { | |
res.header("Access-Control-Allow-Origin", "*"); | |
res.header('Cache-Control', 'no-cache'); | |
res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept"); | |
next(); | |
}); | |
router.get('/', function(req, res) { | |
res.json({ | |
message: 'hooray! welcome to our api!' | |
}); | |
}); | |
router.route('/ur').get(function(req, res) { | |
res.json({ | |
message: 'hooray! welcome to our api!' | |
}); | |
}); | |
router.route('/script/:script') | |
.get(function(req, res) { | |
sql.connect(config, function(err) { | |
if (err) { | |
console.log("Connection Errorin get script: " + err); | |
return; | |
} | |
var request = new sql.Request(); | |
const Q = `select cbt.dbo.CMT_CPT_VIEWS.NAME as NAME , | |
cbt.dbo.CMT_CPT_SV.INTERVAL as INTERVAL, | |
cbt.dbo.CMT_CPT_SV.ORD as ORD, | |
cbt.dbo.CMT_CPT_VIEWS.TITLE as TITLE | |
from cbt.dbo.CMT_CPT_SCRIPTS inner join cbt.dbo.CMT_CPT_SV on ( cbt.dbo.CMT_CPT_SV.SCRIPT = cbt.dbo.CMT_CPT_SCRIPTS.SCRIPT ) | |
inner join cbt.dbo.CMT_CPT_VIEWS on ( cbt.dbo.CMT_CPT_VIEWS.VIEWNUM = cbt.dbo.CMT_CPT_SV.VIEWNUM ) | |
where ( cbt.dbo.CMT_CPT_SCRIPTS.NAME = '`+req.params.script+`') | |
order by 3 | |
`; | |
request.query(Q).then(function(recordset, err) { | |
if (err) { | |
console.log("get script Error: " + err); | |
} | |
console.log(recordset); | |
res.json(recordset); | |
}) | |
}); | |
}); | |
router.route('/rmain') | |
.get(function(req, res) { | |
sql.connect(config, function(err) { | |
if (err) { | |
console.log("Connection Errorin get script: " + err); | |
return; | |
} | |
var request = new sql.Request(); | |
const Q = `select cbt.dbo.PART.PARTNAME as מקט, | |
cbt.dbo.DOCUMENTS.DOCNO as תעודה, | |
cbt.dbo.DOCUMENTS.DETAILS as פרטים, | |
cbt.dbo.SERNUMBERS.SERNUM as מכשיר, | |
cbt.dbo.CMT_TE_STATUS.STATUSDES as סטטוס, | |
coalesce( cbt.dbo.CMT_TRANS_N.TCO , '' ) as גורם_מטפל, | |
substring( system .dbo.tabula_dtoa( cbt.dbo.DOCUMENTS.CURDATE , 'DD/MM/YY' , '' , '' ) + ' ' , 1, 8) as תאריך | |
from cbt.dbo.TRANSORDER inner join cbt.dbo.SERNTRANS on ( cbt.dbo.SERNTRANS.KLINE = cbt.dbo.TRANSORDER.KLINE ) | |
inner join cbt.dbo.CMT_SERNUMBERS on 1 = 1 | |
inner join cbt.dbo.PART on 1 = 1 | |
inner join cbt.dbo.CMT_TE_STATUS on ( cbt.dbo.CMT_TE_STATUS.STATUS = cbt.dbo.CMT_SERNUMBERS.TE_STATUS ) | |
inner join cbt.dbo.SERNUMBERS on ( cbt.dbo.SERNUMBERS.SERN = cbt.dbo.CMT_SERNUMBERS.SERN ) | |
inner join cbt.dbo.DOCUMENTS on ( cbt.dbo.DOCUMENTS.DOC = cbt.dbo.TRANSORDER.DOC ) | |
inner join cbt.dbo.WAREHOUSES on ( cbt.dbo.WAREHOUSES.WARHS = cbt.dbo.SERNUMBERS.WARHS ) | |
left outer join cbt.dbo.CMT_TRANS_N on ( cbt.dbo.CMT_TRANS_N.TRANS = cbt.dbo.TRANSORDER.TRANS ) | |
where not exists ( select 'X' from cbt.dbo.SERNTRANS ST2 | |
where ( ( ST2.TYPE = 'D' ) or ( ST2.TYPE = 'O' ) ) and ( ST2.SERN = cbt.dbo.SERNUMBERS.SERN ) | |
and ( ST2.UDATE > cbt.dbo.DOCUMENTS.CURDATE ) ) | |
and ( cbt.dbo.WAREHOUSES.WARHSNAME = 'RMA' ) | |
and ( cbt.dbo.DOCUMENTS.DOC = cbt.dbo.SERNTRANS.DOC ) | |
and ( cbt.dbo.DOCUMENTS.CURDATE > ( DATEDIFF(minute,\'01-01-1988 00:00\',getdate()) - 1440) ) and ( cbt.dbo.SERNUMBERS.SERN = cbt.dbo.SERNTRANS.SERN ) | |
and ( cbt.dbo.SERNUMBERS.PART = cbt.dbo.PART.PART ) and ( cbt.dbo.SERNUMBERS.WARHS > 0 ) | |
and ( cbt.dbo.CMT_TE_STATUS.STATUS <> 2 ) and ( cbt.dbo.TRANSORDER.TYPE = 'N' ) and ( 1 = 1 ) | |
`; | |
request.query(Q).then(function(recordset, err) { | |
if (err) { | |
console.log("get script Error: " + err); | |
} | |
console.log(recordset); | |
res.json(recordset); | |
}) | |
}); | |
}); | |
router.route('/rmain2') | |
.get(function(req, res) { | |
sql.connect(config, function(err) { | |
if (err) { | |
console.log("Connection Errorin get script: " + err); | |
return; | |
} | |
var request = new sql.Request(); | |
const Q = `select cbt.dbo.PART.PARTNAME as מקט, | |
cbt.dbo.DOCUMENTS.DOCNO as תעודה, | |
cbt.dbo.DOCUMENTS.DETAILS as פרטים, | |
cbt.dbo.SERNUMBERS.SERNUM as מכשיר, | |
cbt.dbo.CMT_TE_STATUS.STATUSDES as סטטוס, | |
coalesce( cbt.dbo.CMT_TRANS_N.TCO , '' ) as גורם_מטפל, | |
substring( system .dbo.tabula_dtoa( cbt.dbo.DOCUMENTS.CURDATE , 'DD/MM/YY' , '' , '' ) + ' ' , 1, 8) as תאריך | |
from cbt.dbo.TRANSORDER inner join cbt.dbo.SERNTRANS on ( cbt.dbo.SERNTRANS.KLINE = cbt.dbo.TRANSORDER.KLINE ) | |
inner join cbt.dbo.CMT_SERNUMBERS on 1 = 1 | |
inner join cbt.dbo.PART on 1 = 1 | |
inner join cbt.dbo.CMT_TE_STATUS on ( cbt.dbo.CMT_TE_STATUS.STATUS = cbt.dbo.CMT_SERNUMBERS.TE_STATUS ) | |
inner join cbt.dbo.SERNUMBERS on ( cbt.dbo.SERNUMBERS.SERN = cbt.dbo.CMT_SERNUMBERS.SERN ) | |
inner join cbt.dbo.DOCUMENTS on ( cbt.dbo.DOCUMENTS.DOC = cbt.dbo.TRANSORDER.DOC ) | |
inner join cbt.dbo.WAREHOUSES on ( cbt.dbo.WAREHOUSES.WARHS = cbt.dbo.SERNUMBERS.WARHS ) | |
left outer join cbt.dbo.CMT_TRANS_N on ( cbt.dbo.CMT_TRANS_N.TRANS = cbt.dbo.TRANSORDER.TRANS ) | |
where not exists ( select 'X' from cbt.dbo.SERNTRANS ST2 | |
where ( ( ST2.TYPE = 'D' ) or ( ST2.TYPE = 'O' ) ) and ( ST2.SERN = cbt.dbo.SERNUMBERS.SERN ) | |
and ( ST2.UDATE > cbt.dbo.DOCUMENTS.CURDATE ) ) | |
and ( cbt.dbo.WAREHOUSES.WARHSNAME = 'RMA' ) | |
and ( cbt.dbo.DOCUMENTS.DOC = cbt.dbo.SERNTRANS.DOC ) | |
and ( cbt.dbo.DOCUMENTS.CURDATE > (DATEDIFF(minute,\'01-01-1988 00:00\',getdate()) - 1440) ) and ( cbt.dbo.SERNUMBERS.SERN = cbt.dbo.SERNTRANS.SERN ) | |
and ( cbt.dbo.SERNUMBERS.PART = cbt.dbo.PART.PART ) and ( cbt.dbo.SERNUMBERS.WARHS > 0 ) | |
and ( cbt.dbo.CMT_TE_STATUS.STATUS <> 2 ) and ( cbt.dbo.TRANSORDER.TYPE = 'N' ) and ( 1 = 1 ) | |
`; | |
request.query(Q).then(function(recordset, err) { | |
if (err) { | |
console.log("get script Error: " + err); | |
} | |
console.log(recordset); | |
res.json(recordset); | |
}) | |
}); | |
}); | |
router.route('/rmaout') | |
.get(function(req, res) { | |
sql.connect(config, function(err) { | |
if (err) { | |
console.log("Connection Errorin get script: " + err); | |
return; | |
} | |
var request = new sql.Request(); | |
const Q = `select cbt.dbo.PART.PARTNAME as מקט, | |
cbt.dbo.DOCUMENTS.DOCNO as תעודה , | |
cbt.dbo.DOCUMENTS.DETAILS as פרטים, | |
cbt.dbo.SERNUMBERS.SERNUM as מכשיר , | |
coalesce( cbt.dbo.CMT_TE_STATUS.STATUSDES , '' ) as סטטוס , | |
substring( system .dbo.tabula_dtoa( cbt.dbo.DOCUMENTS.CURDATE , 'DD/MM/YY' , '' , '' ) + ' ' , 1, 8) as תאריך | |
from cbt.dbo.TRANSORDER inner join cbt.dbo.SERNTRANS on ( cbt.dbo.SERNTRANS.KLINE = cbt.dbo.TRANSORDER.KLINE ) | |
inner join cbt.dbo.CMT_SERNUMBERS on 1 = 1 | |
inner join cbt.dbo.WAREHOUSES on ( cbt.dbo.WAREHOUSES.WARHSNAME = 'RMA' ) | |
inner join cbt.dbo.SERNUMBERS on ( cbt.dbo.SERNUMBERS.SERN = cbt.dbo.CMT_SERNUMBERS.SERN ) | |
inner join cbt.dbo.DOCUMENTS on ( cbt.dbo.DOCUMENTS.DOC = cbt.dbo.TRANSORDER.DOC ) | |
inner join cbt.dbo.PART on ( cbt.dbo.PART.PART = cbt.dbo.SERNUMBERS.PART ) | |
left outer join cbt.dbo.CMT_TE_STATUS on ( cbt.dbo.CMT_TE_STATUS.STATUS = cbt.dbo.CMT_SERNUMBERS.TE_STATUS ) | |
where ( cbt.dbo.DOCUMENTS.DOC = cbt.dbo.SERNTRANS.DOC ) and ( cbt.dbo.DOCUMENTS.CURDATE > ( DATEDIFF(minute,\'01-01-1988 00:00\',getdate()) - 14400 ) ) | |
and ( cbt.dbo.SERNUMBERS.SERN = cbt.dbo.SERNTRANS.SERN ) and ( cbt.dbo.SERNUMBERS.WARHS > 0 ) | |
and ( cbt.dbo.SERNUMBERS.WARHS = cbt.dbo.WAREHOUSES.WARHS ) and ( cbt.dbo.TRANSORDER.TYPE = 'D' ) and ( 1 = 1 ) | |
`; | |
request.query(Q).then(function(recordset, err) { | |
if (err) { | |
console.log("get script Error: " + err); | |
} | |
console.log(recordset); | |
res.json(recordset); | |
}) | |
}); | |
}); | |
router.route('/rmag/:timeframe') | |
.get(function(req, res) { | |
sql.connect(config, function(err) { | |
if (err) { | |
console.log("Connection Error in RMA Graph: " + err); | |
return; | |
} | |
var request = new sql.Request(); | |
const Q = `select (DATEDIFF(minute,\'01-01-1988 00:00\',getdate()) - cbt.dbo.DOCUMENTS.CURDATE ) / (case when ( `+req.params.timeframe+` ) = 0 then 1 else ( `+req.params.timeframe+` ) end) as X, | |
coalesce( cbt.dbo.CMT_TE_STATUS.STATUSDES , '' ) as GROUPS, sum( 1 ) as Y | |
from cbt.dbo.TRANSORDER inner join cbt.dbo.SERNTRANS on ( cbt.dbo.SERNTRANS.KLINE = cbt.dbo.TRANSORDER.KLINE ) | |
inner join cbt.dbo.CMT_SERNUMBERS on 1 = 1 | |
inner join cbt.dbo.SERNUMBERS on ( cbt.dbo.SERNUMBERS.SERN = cbt.dbo.CMT_SERNUMBERS.SERN ) | |
inner join cbt.dbo.DOCUMENTS on ( cbt.dbo.DOCUMENTS.DOC = cbt.dbo.TRANSORDER.DOC ) | |
inner join cbt.dbo.WAREHOUSES on ( cbt.dbo.WAREHOUSES.WARHS = cbt.dbo.SERNUMBERS.WARHS ) | |
left outer join cbt.dbo.CMT_TE_STATUS on ( cbt.dbo.CMT_TE_STATUS.STATUS = cbt.dbo.CMT_SERNUMBERS.TE_STATUS ) | |
where not exists ( select 'X' | |
from cbt.dbo.SERNTRANS ST2 | |
where ( ( ST2.TYPE = 'D' ) or ( ST2.TYPE = 'O' ) ) and ( ST2.SERN = cbt.dbo.SERNUMBERS.SERN ) and ( ST2.UDATE > cbt.dbo.DOCUMENTS.CURDATE ) ) and ( cbt.dbo.WAREHOUSES.WARHSNAME = 'RMA' ) and ( cbt.dbo.CMT_TE_STATUS.STATUS <> 2 ) and ( cbt.dbo.DOCUMENTS.DOC = cbt.dbo.SERNTRANS.DOC ) | |
and ( cbt.dbo.DOCUMENTS.CURDATE > ( DATEDIFF(minute,\'01-01-1988 00:00\',getdate()) - ( `+req.params.timeframe+` * 60 ) ) ) and ( cbt.dbo.SERNUMBERS.SERN = cbt.dbo.SERNTRANS.SERN ) and ( cbt.dbo.SERNUMBERS.WARHS > 0 ) and ( cbt.dbo.TRANSORDER.TYPE = 'N' ) and ( 1 = 1 ) | |
group by ( DATEDIFF(minute,\'01-01-1988 00:00\',getdate()) - cbt.dbo.DOCUMENTS.CURDATE ) / (case when ( `+req.params.timeframe+` ) = 0 then 1 else ( `+req.params.timeframe+` ) end) | |
, coalesce( cbt.dbo.CMT_TE_STATUS.STATUSDES , '' ) | |
having count(*) > 0 | |
order by 1 , 2 `; | |
request.query(Q).then(function(recordset, err) { | |
if (err) { | |
console.log("get script Error: " + err); | |
} | |
console.log(recordset); | |
res.json(recordset); | |
}) | |
}); | |
}); | |
router.route('/graph/pdemand1') | |
.get(function(req, res) { | |
sql.connect(config, function(err) { | |
if (err) { | |
console.log("Connection Error: " + err); | |
return; | |
} | |
Q = 'SELECT MAX(CURDATE) AS MX FROM PURDEMANDS WHERE AUTO = \'A\''; | |
var request = new sql.Request(); | |
request.query(Q).then(function(recordset, err) { | |
if (err) { | |
console.log("Query Error: " + err); | |
} | |
//res.json(recordset); | |
return recordset[0] | |
}).then(function(rs) { | |
const Q = `select reverse( reverse(substring(reverse( '' + case when ( ( coalesce( system.dbo.USERSB.USERB , 0 ) > 0 ) ) then ( system .dbo.tabula_strpiece( reverse( coalesce( system.dbo.USERSB.SNAME , '' ) ) , ' ' , 2 , 1 ) ) else ( 'ףתושמ' ) end ) , 1, 15) ) ) as X, | |
substring( case when ( ( cbt.dbo.PRDITEMS.CLOSED = 'C' ) ) then ( 'סגור' ) else ( 'פתוח' ) end + '' , 1, 10) as GROUPS, | |
sum( 1 ) as Y | |
from cbt.dbo.PURDEMANDS inner join cbt.dbo.PRDITEMS on ( cbt.dbo.PRDITEMS.PURDEMAND = cbt.dbo.PURDEMANDS.PURDEMAND ) | |
left outer join cbt.dbo.CUSTPART on ( cbt.dbo.CUSTPART.PART = cbt.dbo.PRDITEMS.PART ) | |
left outer join cbt.dbo.CMT_CUST_STAFF on ( coalesce( cbt.dbo.CMT_CUST_STAFF.ROLE , 0 ) = 2 ) and ( cbt.dbo.CMT_CUST_STAFF.CUST = coalesce( cbt.dbo.CUSTPART.CUST , 0 ) ) | |
left outer join system.dbo.USERSB on ( system.dbo.USERSB.USERB = coalesce( cbt.dbo.CMT_CUST_STAFF.T$USER , 0 ) ) | |
where ( cbt.dbo.PURDEMANDS.AUTO = 'A' ) and ( cbt.dbo.PURDEMANDS.CURDATE = `+rs.MX+` ) and ( 1 = 1 ) | |
group by reverse( reverse(substring(reverse( '' + case when ( ( coalesce( system.dbo.USERSB.USERB , 0 ) > 0 ) ) then ( system .dbo.tabula_strpiece( reverse( coalesce( system.dbo.USERSB.SNAME , '' ) ) , ' ' , 2 , 1 ) ) else ( 'ףתושמ' ) end ) , 1, 15) ) ) | |
, substring( case when ( ( cbt.dbo.PRDITEMS.CLOSED = 'C' ) ) then ( 'סגור' ) else ( 'פתוח' ) end + '' , 1, 10) | |
having count(*) > 0 | |
order by 1 , 2 ` | |
request.query(Q).then(function(recordset, err) { | |
if (err) { | |
console.log("purdeMAND Error: " + err); | |
} | |
console.log(recordset); | |
res.json(recordset); | |
}) | |
}); | |
}); | |
}); | |
router.route('/graph/pdemand2') | |
.get(function(req, res) { | |
sql.connect(config, function(err) { | |
if (err) { | |
console.log("Connection Error: " + err); | |
return; | |
} | |
var request = new sql.Request(); | |
const Q = `select ( DATEDIFF(minute,\'01-01-1988 00:00\',getdate()) - cbt.dbo.PURDEMANDS.CURDATE ) / (case when ( 1440 ) = 0 then 1 else ( 1440 ) end) as X, | |
reverse( reverse(substring(reverse( ' ' + case when ( ( coalesce( system.dbo.USERSB.USERB , 0 ) > 0 ) ) then ( system .dbo.tabula_strpiece( reverse( coalesce( system.dbo.USERSB.SNAME , '' ) ) , ' ' , 2 , 1 ) ) else ( 'ףתושמ' ) end ) , 1, 15) ) ) as GROUPS, | |
sum( 1 ) as Y | |
from cbt.dbo.PURDEMANDS inner join cbt.dbo.PRDITEMS on ( cbt.dbo.PRDITEMS.CLOSED <> 'C' ) and ( cbt.dbo.PRDITEMS.PURDEMAND = cbt.dbo.PURDEMANDS.PURDEMAND ) | |
left outer join cbt.dbo.CUSTPART on ( cbt.dbo.CUSTPART.PART = cbt.dbo.PRDITEMS.PART ) | |
left outer join cbt.dbo.CMT_CUST_STAFF on ( coalesce( cbt.dbo.CMT_CUST_STAFF.ROLE , 0 ) = 2 ) and ( cbt.dbo.CMT_CUST_STAFF.CUST = coalesce( cbt.dbo.CUSTPART.CUST , 0 ) ) | |
left outer join system.dbo.USERSB on ( system.dbo.USERSB.USERB = coalesce( cbt.dbo.CMT_CUST_STAFF.T$USER , 0 ) ) | |
where ( cbt.dbo.PURDEMANDS.CURDATE < ( DATEDIFF(minute,\'01-01-1988 00:00\',getdate()) - 2800 ) ) and ( cbt.dbo.PURDEMANDS.CURDATE > ( DATEDIFF(minute,\'01-01-1988 00:00\',getdate()) - ( 60 * 1440 ) ) ) and ( 1 = 1 ) | |
group by ( DATEDIFF(minute,\'01-01-1988 00:00\',getdate()) - cbt.dbo.PURDEMANDS.CURDATE ) / (case when ( 1440 ) = 0 then 1 else ( 1440 ) end) | |
, reverse( reverse(substring(reverse( ' ' + case when ( ( coalesce( system.dbo.USERSB.USERB , 0 ) > 0 ) ) then ( system .dbo.tabula_strpiece( reverse( coalesce( system.dbo.USERSB.SNAME , '' ) ) , ' ' , 2 , 1 ) ) else ( 'ףתושמ' ) end ) , 1, 15) ) ) | |
having count(*) > 0 | |
order by 1 , 2 `; | |
request.query(Q).then(function(recordset, err) { | |
if (err) { | |
console.log("pudemand2 Error: " + err); | |
} | |
console.log(recordset); | |
res.json(recordset); | |
}) | |
}); | |
}); | |
router.route('/graph/porder2') | |
.get(function(req, res) { | |
sql.connect(config, function(err) { | |
if (err) { | |
console.log("Connection Error: " + err); | |
return; | |
} | |
var request = new sql.Request(); | |
const Q = `select ( 1 + ( DATEDIFF(minute,\'01-01-1988 00:00\',getdate()) - cbt.dbo.PORDERS.CURDATE ) / (case when ( 1440 ) = 0 then 1 else ( 1440 ) end) / (case when ( 7 ) = 0 then 1 else ( 7 ) end) ) as X, | |
cbt.dbo.PORDSTATS.STATDES as GROUPS, | |
sum( 1 ) as Y | |
from cbt.dbo.PORDERS inner join cbt.dbo.PORDSTATS on ( cbt.dbo.PORDSTATS.PORDSTAT = cbt.dbo.PORDERS.PORDSTAT ) | |
where ( ( cbt.dbo.PORDERS.PORDSTAT = 6 ) or ( ( cbt.dbo.PORDERS.PORDSTAT = - ( 4 ) ) or ( ( cbt.dbo.PORDERS.PORDSTAT = 4 ) or ( ( cbt.dbo.PORDERS.PORDSTAT = - ( 10 ) ) or ( ( cbt.dbo.PORDERS.PORDSTAT = - ( 9 ) ) or ( ( cbt.dbo.PORDERS.PORDSTAT = 7 ) or ( cbt.dbo.PORDERS.PORDSTAT = - ( 6 ) ) ) ) ) ) ) ) and ( cbt.dbo.PORDERS.CLOSED <> 'C' ) and ( 1 = 1 ) | |
group by ( 1 + ( DATEDIFF(minute,\'01-01-1988 00:00\',getdate()) - cbt.dbo.PORDERS.CURDATE ) / (case when ( 1440 ) = 0 then 1 else ( 1440 ) end) / (case when ( 7 ) = 0 then 1 else ( 7 ) end) ) | |
, cbt.dbo.PORDSTATS.STATDES | |
having count(*) > 0 | |
order by 1 , 2 `; | |
request.query(Q).then(function(recordset, err) { | |
if (err) { | |
console.log("porders2 Error: " + err); | |
} | |
console.log(recordset); | |
res.json(recordset); | |
}) | |
}); | |
}); | |
router.route('/graph/porder1') | |
.get(function(req, res) { | |
sql.connect(config, function(err) { | |
if (err) { | |
console.log("Connection Error: " + err); | |
return; | |
} | |
var request = new sql.Request(); | |
const Q = `select reverse( reverse(substring(reverse( ' ' + case when ( ( coalesce( system.dbo.USERSB.USERB , 0 ) > 0 ) ) then ( system .dbo.tabula_strpiece( reverse( coalesce( system.dbo.USERSB.SNAME , '' ) ) , ' ' , 2 , 1 ) ) else ( 'ףתושמ' ) end ) , 1, 15) ) ) as X, | |
cbt.dbo.PORDSTATS.STATDES as GROUPS, | |
sum( 1 ) as Y | |
from cbt.dbo.PORDERITEMS inner join cbt.dbo.PORDERS on ( cbt.dbo.PORDERS.ORD = cbt.dbo.PORDERITEMS.ORD ) | |
inner join cbt.dbo.PORDSTATS on ( cbt.dbo.PORDSTATS.PORDSTAT = cbt.dbo.PORDERS.PORDSTAT ) | |
left outer join cbt.dbo.CUSTPART on ( cbt.dbo.CUSTPART.PART = cbt.dbo.PORDERITEMS.PART ) | |
left outer join cbt.dbo.CMT_CUST_STAFF on ( coalesce( cbt.dbo.CMT_CUST_STAFF.ROLE , 0 ) = 2 ) and ( cbt.dbo.CMT_CUST_STAFF.CUST = coalesce( cbt.dbo.CUSTPART.CUST , 0 ) ) | |
left outer join system.dbo.USERSB on ( system.dbo.USERSB.USERB = coalesce( cbt.dbo.CMT_CUST_STAFF.T$USER , 0 ) ) | |
where ( ( cbt.dbo.PORDERS.PORDSTAT = 6 ) or ( ( cbt.dbo.PORDERS.PORDSTAT = - ( 4 ) ) or ( ( cbt.dbo.PORDERS.PORDSTAT = 4 ) or ( ( cbt.dbo.PORDERS.PORDSTAT = - ( 10 ) ) or ( ( cbt.dbo.PORDERS.PORDSTAT = - ( 9 ) ) or ( ( cbt.dbo.PORDERS.PORDSTAT = 7 ) or ( ( cbt.dbo.PORDERS.PORDSTAT = - ( 6 ) ) or ( cbt.dbo.PORDERS.PORDSTAT = 5 ) ) ) ) ) ) ) ) and ( 1 = 1 ) | |
group by reverse( reverse(substring(reverse( ' ' + case when ( ( coalesce( system.dbo.USERSB.USERB , 0 ) > 0 ) ) then ( system .dbo.tabula_strpiece( reverse( coalesce( system.dbo.USERSB.SNAME , '' ) ) , ' ' , 2 , 1 ) ) else ( 'ףתושמ' ) end ) , 1, 15) ) ) | |
, cbt.dbo.PORDSTATS.STATDES | |
having count(*) > 0 `; | |
request.query(Q).then(function(recordset, err) { | |
if (err) { | |
console.log("porders2 Error: " + err); | |
} | |
console.log(recordset); | |
res.json(recordset); | |
}) | |
}); | |
}); | |
router.route('/graph/porder3') | |
.get(function(req, res) { | |
sql.connect(config, function(err) { | |
if (err) { | |
console.log("Connection Error: " + err); | |
return; | |
} | |
var request = new sql.Request(); | |
const Q = `select ( DATEDIFF(minute,\'01-01-1988 00:00\',getdate()) - cbt.dbo.PORDERITEMS.DUEDATE ) / (case when ( ( 1440 * 7 ) ) = 0 then 1 else ( ( 1440 * 7 ) ) end) as X, | |
cbt.dbo.PORDSTATS.STATDES as GROUPS, | |
sum( 1 ) as Y | |
from cbt.dbo.PORDERITEMS inner join cbt.dbo.PORDERS on ( cbt.dbo.PORDERS.ORD = cbt.dbo.PORDERITEMS.ORD ) | |
inner join cbt.dbo.PORDSTATS on ( cbt.dbo.PORDSTATS.PORDSTAT = cbt.dbo.PORDERS.PORDSTAT ) | |
where ( cbt.dbo.PORDERS.PORDSTAT <> 0 ) and ( cbt.dbo.PORDERS.PORDSTAT <> - ( 5 ) ) and ( cbt.dbo.PORDERS.PORDSTAT <> - ( 8 ) ) and ( cbt.dbo.PORDERS.PORDSTAT <> - ( 6 ) ) and ( cbt.dbo.PORDERS.CLOSED <> 'C' ) and ( convert(decimal(19,2), cbt.dbo.PORDERITEMS.QPRICE) <> 0 ) and ( cbt.dbo.PORDERITEMS.ABALANCE > 0 ) and ( cbt.dbo.PORDERITEMS.CLOSED <> 'C' ) and ( cbt.dbo.PORDERITEMS.DUEDATE < 15420425 ) and ( 1 = 1 ) | |
group by ( DATEDIFF(minute,\'01-01-1988 00:00\',getdate()) - cbt.dbo.PORDERITEMS.DUEDATE ) / (case when ( ( 1440 * 7 ) ) = 0 then 1 else ( ( 1440 * 7 ) ) end) | |
, cbt.dbo.PORDSTATS.STATDES | |
having count(*) > 0 | |
order by 1 , 2 `; | |
request.query(Q).then(function(recordset, err) { | |
if (err) { | |
console.log("porders2 Error: " + err); | |
} | |
console.log(recordset); | |
res.json(recordset); | |
}) | |
}); | |
}); | |
router.route('/graph/serial1') | |
.get(function(req, res) { | |
sql.connect(config, function(err) { | |
if (err) { | |
console.log("Connection Error: " + err); | |
return; | |
} | |
var request = new sql.Request(); | |
const Q = `select REVERSE(system.dbo.USERS.USERLOGIN) as X, | |
cbt.dbo.SERIALSTATUS.SERIALSTATUSDES as GROUPS, | |
sum( 1 ) as Y | |
from cbt.dbo.SERIALA inner join cbt.dbo.SERIAL on ( cbt.dbo.SERIAL.SERIAL = cbt.dbo.SERIALA.SERIAL ) | |
inner join system.dbo.USERS on ( system.dbo.USERS.T$USER = cbt.dbo.SERIALA.OWNER ) | |
inner join cbt.dbo.SERIALSTATUS on ( cbt.dbo.SERIALSTATUS.SERIALSTATUS = cbt.dbo.SERIALA.SERIALSTATUS ) | |
where ( cbt.dbo.SERIAL.CLOSED <> 'C' ) and ( cbt.dbo.SERIALA.OWNER > 1 ) and ( 1 = 1 ) | |
group by REVERSE(system.dbo.USERS.USERLOGIN), cbt.dbo.SERIALSTATUS.SERIALSTATUSDES | |
having count(*) > 0 | |
order by 1 , 2 `; | |
request.query(Q).then(function(recordset, err) { | |
if (err) { | |
console.log("serial1 Error: " + err); | |
} | |
console.log(recordset); | |
res.json(recordset); | |
}) | |
}); | |
}); | |
router.route('/graph/serial2') | |
.get(function(req, res) { | |
sql.connect(config, function(err) { | |
if (err) { | |
console.log("Connection Error: " + err); | |
return; | |
} | |
var request = new sql.Request(); | |
const Q = `select ( DATEDIFF(minute,\'01-01-1988 00:00\',getdate()) - cbt.dbo.SERIAL.CURDATE ) / (case when ( 1440 ) = 0 then 1 else ( 1440 ) end) / (case when ( 7 ) = 0 then 1 else ( 7 ) end) as X, | |
REVERSE(system.dbo.USERS.USERLOGIN) as GROUPS, | |
sum( 1 ) as Y | |
from cbt.dbo.SERIAL inner join cbt.dbo.SERIALA on ( cbt.dbo.SERIALA.SERIAL = cbt.dbo.SERIAL.SERIAL ) | |
inner join system.dbo.USERS on ( system.dbo.USERS.T$USER = cbt.dbo.SERIALA.OWNER ) | |
where ( cbt.dbo.SERIALA.OWNER > 1 ) and ( cbt.dbo.SERIAL.CLOSED <> 'C' ) and ( 1 = 1 ) | |
group by ( DATEDIFF(minute,\'01-01-1988 00:00\',getdate()) - cbt.dbo.SERIAL.CURDATE ) / (case when ( 1440 ) = 0 then 1 else ( 1440 ) end) / (case when ( 7 ) = 0 then 1 else ( 7 ) end) | |
, REVERSE(system.dbo.USERS.USERLOGIN) | |
having count(*) > 0 | |
order by 1 `; | |
request.query(Q).then(function(recordset, err) { | |
if (err) { | |
console.log("serial1 Error: " + err); | |
} | |
console.log(recordset); | |
res.json(recordset); | |
}) | |
}); | |
}); | |
router.route('/getp/:partname') | |
.get(function(req, res) { | |
sql.connect(config, function(err) { | |
if (err) { | |
console.log("Connection Error: " + err); | |
return; | |
} | |
Q = 'SELECT PART,PARTNAME,PARTDES,SECONDCOST FROM PART WHERE PARTNAME = \'' + req.params.partname + '\''; | |
var request = new sql.Request(); | |
request.query(Q).then(function(recordset, err) { | |
if (err) { | |
console.log("Query Error: " + err); | |
} | |
/*res.json(recordset);*/ | |
recordset[0].PARTDES = recordset[0].PARTDES.split("").reverse().join(""); | |
return recordset[0] | |
}).then(function(rs) { | |
p = rs.PART; | |
B = 'SELECT SUM(BALANCE)/1000 AS BAL FROM WARHSBAL WHERE BALANCE > 0 AND WARHS <>0 AND PART = ' + p; | |
request.query(B).then(function(recordset, err) { | |
if (err) { | |
console.log("Query2 Error: " + err); | |
} | |
rs.BAL = recordset[0].BAL; | |
//console.log(B); | |
return rs; | |
}).then(function(rs) { | |
S = 'SELECT EXTFILENAME,EXTFILEDES FROM PARTEXTFILE WHERE PART = ' + rs.PART; | |
request.query(S).then(function(recordset, err) { | |
if (err) { | |
console.log("Query3 Error: " + err); | |
} | |
rs.EXTFILE = recordset; | |
//console.log("final--",rs); | |
res.json(rs); | |
}) | |
}); | |
}); | |
}); | |
}); | |
app.use('/', router); | |
app.listen(port); | |
console.log('Magic happens on port ' + port); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment