var express = require('express');
var router = express.Router();

var options = require('./dbconfig');

//
// Postgresql
//
var Pool = require('pg').Pool;
var config = {
  user: 'matjaz',
  password: 'mesnjak',
  database: 'supervizor',
  host: 'server.dev',
  port: 5432,
  max: 10,
  idleTimeoutMillis: 30000,
};
var pool = new Pool(config);

router.get('/:sifrapu/:id?', function (req, res, next) {
  if(!req.params.id) req.params.id = 0;
  var odgovor = { 
    title: 'Naš denar', 
    stran: req.params.id, 
    next: parseInt(req.params.id) + 1, 
    prev: parseInt(req.params.id) - 1, 
    sifrapu: req.params.sifrapu, 
    dutbdelez: -1,
    davcna: -1,
    grafEvidence: -1,
    tecajnica: -1,
    narociladelez_podj: -1,
  }
  var stevec = 0;
  var vseZahteve = 7;

  pool.query('SELECT * FROM rpu WHERE sifra_pu = $1 LIMIT 1', [req.params.sifrapu], function (err, subjResult) {
    if(err) {
      throw err;
    }
    odgovor.subjekt = subjResult.rows;
    stevec++;
    console.log('1');
    if(stevec == vseZahteve) {
      res.render('organ', odgovor);        
    }
  })

  pool.query('SELECT organ, maticna, davcna, sifra_pu, naslov, posta, tip_omejitve, ps_naziv, ps_maticna, ps_davcna, mid, ps_naslov, ps_posta, (extract(day FROM datum_od) || \'.\' ||  extract(month from datum_od) || \'.\' || extract(year from datum_od)) DATUM_OD, (extract(day FROM datum_do) || \'.\' ||  extract(month FROM datum_do) || \'.\' || extract(year FROM datum_do)) DATUM_DO FROM omejitve WHERE sifra_pu = $1 and tip_omejitve = \'ps\'', [req.params.sifrapu], function (err, omejitve) {
    if(err) {
      throw err;
    }
    odgovor.omejitve = omejitve.rows;
    stevec++;
    console.log('2')
    if(stevec == vseZahteve) {
      res.render('organ', odgovor);        
    }
  })

  pool.query('SELECT * FROM lokacija WHERE davcna_stevilka = (SELECT davcna FROM rpu WHERE sifra_pu = $1 LIMIT 1) LIMIT 1', [req.params.sifrapu], function (err, lokacija_JS) {
    if(err) {
      throw err;
    }
    odgovor.lokacijaJS = lokacija_JS.rows;
    stevec++;
    console.log('3')
    if(stevec == vseZahteve) {
      res.render('organ', odgovor);        
    }
  })
  
  pool.query('SELECT sum(vsota_prejemkov) STOLPIC, leto, mesec FROM dupu WHERE sifra_pu = $1 GROUP BY leto, mesec', [req.params.sifrapu], function (err, transResult) {
    if(err) {
      throw err;
    }
    var date = new Date();
    var mesec = date.getMonth() + 1;
    var leto = date.getFullYear();
    var vsehMesecev = (leto - 2003) * 12 + mesec;
    var stolpci = [];
    for (var i = 0; i < vsehMesecev; i++) {
      stolpci.push(0);
    }
    for (var j = 0; j < transResult.rows.length; j++) {
      stolpci[((transResult.rows[j].leto - 2003) * 12 + transResult.rows[j].mesec)-1] = transResult.rows[j].stolpic;
    }
    odgovor.transakcije = stolpci;
    stevec++;
    console.log('4')
    if(stevec == vseZahteve) {
      res.render('organ', odgovor);        
    }
  })
  
  pool.query('SELECT *, (extract(day FROM datum_objave) || \'.\' || extract(month FROM datum_objave) || \'.\' || extract(year FROM datum_objave)) datum FROM narocila WHERE sifra_pu_narocnika = $1 ORDER BY datum_objave DESC', [req.params.sifrapu], function (err, narocila) {
    if(err) {
      throw err
    }
    odgovor.narocila = narocila.rows
    stevec++
    console.log("5")
    if(stevec == vseZahteve) {
      res.render('organ', odgovor)
    }
  })
  
  pool.query('SELECT sum(vrednost_brez_ddv) znesek, ponudnik FROM narocila WHERE sifra_pu_narocnika = $1 GROUP BY ponudnik ORDER BY znesek DESC LIMIT 5', [req.params.sifrapu], function (err, narocila) {
    if(err) {
      throw err
    }
    odgovor.narociladelez_organ = narocila.rows
    stevec++
    console.log("6")
    if(stevec == vseZahteve) {
      res.render('organ', odgovor)
    }
  })

  pool.query('SELECT sum(vrednost_brez_ddv) skupni_znesek FROM narocila WHERE sifra_pu_narocnika = $1', [req.params.sifrapu], function(err, vsotanarocil){
    if(err) {
      throw err
    }
    odgovor.vsotanarocil = vsotanarocil.rows[0].skupni_znesek
    stevec++
    console.log("7")
    if(stevec == vseZahteve) {
      res.render('organ', odgovor)
    }
  })
});

module.exports = router;