var express = require('express')
const router = express.Router();
const db = require("../../utils/database");
var moment = require('moment')


//Not using now
router.post('/getFilterData', (req, res) => {
    const startDate = req.body.startDate
    const endDate = req.body.endDate
    console.log(startDate,endDate)
    db.getConnection((err, connection) => {
        if (err) {
          console.log("database not connected error: >>>", err);
          throw err;
        }
        console.log("get Connection");
        connection.query(`SELECT * FROM finehhdi_eDefect.defect WHERE DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') >= "${startDate}" AND DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') <= "${endDate}"`, (err, data) => {
            connection.release()
            if (err) {
                res.status(500).send({
                    msg: 'err getting result',
                    data: []
                })
            }
            else {
                res.status(200).send({
                    msg: 'success',
                    data: data
                })
            }
        })
        console.log("release Connection");
      });

})


// Machine Filter Data and geting machine sum
router.post('/CountMachineFilter', (req, res) => {
    console.log('req from Machine')
    const startDate = req.body.startDate
    const endDate = req.body.endDate
    console.log(startDate,endDate)

    db.query(`SELECT machine, COUNT(machine) as Total FROM finehhdi_eDefect.defect WHERE DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') >= '${startDate}' AND DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') <='${endDate}' GROUP BY machine HAVING COUNT(machine) > 0`, (err, data) => {

        if (err) {
            res.status(500).send({
                msg: 'err getting result',
                filter: []
            })
        }
        else {
            res.status(200).send({
                msg: 'success',
                filter: data
            })
        }
    })
})

// Area/Department Filter Data and geting area/dept sum
router.post('/CountAreaFilter', (req, res) => {
    console.log('req from Area/Department')
    const startDate = req.body.startDate
    const endDate = req.body.endDate
    console.log(startDate,endDate)
    db.query(`SELECT department, COUNT(department) as Total FROM finehhdi_eDefect.defect WHERE DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') >= '${startDate}' AND DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') <='${endDate}' GROUP BY department HAVING COUNT(department) > 0 `, (err, data) => {

        if (err) {
            res.status(500).send({
                msg: 'err getting result',
                filter: []
            })
        }
        else {
            res.status(200).send({
                msg: 'success',
                filter: data
            })
        }
    })
})

// Defect/Anamoly Filter Data and geting defect/anamoly sum
router.post('/CountDefectFilter', (req, res) => {
    console.log('req from Defect/Anamoly')
    const startDate = req.body.startDate
    const endDate = req.body.endDate
    console.log(startDate,endDate)

    db.query(`SELECT anamoly, COUNT(anamoly) as Total FROM finehhdi_eDefect.defect WHERE DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') >= '${startDate}' AND DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') <='${endDate}' GROUP BY anamoly HAVING COUNT(anamoly) > 0`, (err, data) => {

        if (err) {
            res.status(500).send({
                msg: 'err getting result',
                filter: []
            })
        }
        else {
            res.status(200).send({
                msg: 'success',
                filter: data
            })
        }
    })
})

// Status Filter Data and geting status sum
router.post('/CountStatusFilter', (req, res) => {
    console.log('req from Status Filter')

    const startDate = req.body.startDate
    const endDate = req.body.endDate
    console.log(startDate,endDate)

    db.query(`SELECT defectStatus, COUNT(defectStatus) as Total FROM finehhdi_eDefect.defect WHERE DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') >= '${startDate}' AND DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') <='${endDate}' GROUP BY defectStatus HAVING COUNT(defectStatus) > 0`, (err, data) => {

        if (err) {
            res.status(500).send({
                msg: 'err getting result',
                filter: []
            })
        }
        else {
            res.status(200).send({
                msg: 'success',
                filter: data
            })
        }
    })
})

// new changes 30-ep
// Status Filter Data and geting status sum
router.post('/CountCreateByFilter', (req, res) => {
    console.log('req from Status Filter Data')

    const startDate = req.body.startDate
    const endDate = req.body.endDate
    console.log(startDate,endDate)

    db.query(`SELECT wfound, COUNT(wfound) as Total FROM finehhdi_eDefect.defect WHERE DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') >= '${startDate}' AND DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') <='${endDate}' GROUP BY wfound HAVING COUNT(wfound) > 0`, (err, data) => {

        if (err) {
            res.status(500).send({
                msg: 'err getting result',
                filter: []
            })
        }
        else {
            res.status(200).send({
                msg: 'success',
                filter: data
            })
        }
    })
})

//for open defect

router.post('/CountOpenDefectFilter', (req, res) => {
    console.log('req from open defect')

    const startDate = req.body.startDate
    const endDate = req.body.endDate
    console.log(startDate,endDate)

    db.query(`SELECT anamoly, COUNT(anamoly) as Total FROM finehhdi_eDefect.defect WHERE defectStatus='Open' AND DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') >='${startDate}' AND DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') <='${endDate}' GROUP BY anamoly HAVING COUNT(anamoly) > 0`, (err, data) => {

        if (err) {
            res.status(500).send({
                msg: 'err getting result',
                filter: []
            })
        }
        else {
            res.status(200).send({
                msg: 'success',
                filter: data
            })
        }
    })
})

// for open defect duration 

router.post('/CountOpenDefectDurationFilter', (req, res) => {
    console.log('req from open defect duration ')

    const startDate = req.body.startDate
    const endDate = req.body.endDate
    console.log(startDate,endDate)

    db.query(`SELECT anamoly ,DATEDIFF(CURRENT_TIMESTAMP, createdOn) AS DAYS FROM finehhdi_eDefect.defect where defectStatus='Open' AND DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') >='${startDate}' AND DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') <='${endDate}'`, (err, data) => {

        if (err) {
            res.status(500).send({
                msg: 'err getting result',
                filter: []
            })
        }
        else {
            res.status(200).send({
                msg: 'success',
                filter: data
            })
        }
    })
})

// Repeted Defect Part
router.post('/CountRepeatedDefectPartFilter', (req, res) => {
    console.log('req from Repeted Defect Part ')

    const startDate = req.body.startDate
    const endDate = req.body.endDate
    console.log(startDate,endDate)

    db.query(`SELECT defectPart, COUNT(defectPart) as Total FROM finehhdi_eDefect.defect WHERE DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') >= '${startDate}' AND DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') <='${endDate}' GROUP BY defectPart HAVING COUNT(defectPart) > 0`, (err, data) => {

        if (err) {
            res.status(500).send({
                msg: 'err getting result',
                filter: []
            })
        }
        else {
            res.status(200).send({
                msg: 'success',
                filter: data
            })
        }
    })
})

// for solved defect with user name
router.post('/CountSolvedDefectFilter', (req, res) => {
    console.log('req from open defect duration ')

    const startDate = req.body.startDate
    const endDate = req.body.endDate
    console.log(startDate,endDate)

    db.query(`SELECT createdBy AS Person,anamoly, COUNT(anamoly) as Total FROM finehhdi_eDefect.defect WHERE defectStatus='Solved' AND DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') >='${startDate}' AND DATE_FORMAT(STR_TO_DATE(createdOn,'%Y-%m-%d'), '%Y-%m-%d') <='${endDate}' GROUP BY anamoly HAVING COUNT(anamoly) > 0`, (err, data) => {

        if (err) {
            res.status(500).send({
                msg: 'err getting result',
                filter: []
            })
        }
        else {
            res.status(200).send({
                msg: 'success',
                filter: data
            })
        }
    })
})



module.exports = router;
