cbt_charts

gistfile1.txt

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);