async function rellenarEstados() {
  Model.knex(knexOc);
  let resultOc = await PERF_INFO_RUTAS_PREPAGO.query().select(
    PERF_INFO_RUTAS_PREPAGO.raw("DISTINCT ESTADO, REGION, SUBREGION")
  );
  Model.knex(knexPg);
  for (let i in resultOc) {
    let resultIdRegion = await REGION.query()
      .select("ID")
      .where("REGION", resultOc[i].REGION);

    let resultIdSubregion = await SUBREGION.query()
      .select("ID")
      .where("SUBREGION", resultOc[i].SUBREGION);

    if (typeof resultIdRegion[0] === "undefined") {
      continue;
    }

    if (typeof resultIdSubregion[0] === "undefined") {
      continue;
    }
    await ESTADO.query()
      .insert({
        ESTADO: resultOc[i].ESTADO,
        ID_REGION: resultIdRegion[0].ID,
        ID_SUBREGION: resultIdSubregion[0].ID
      })
      .returning("ID");
  }
  //await CLUST.query().insert({ CLUST: "No Definida" }).returning("ID");
  console.log("Estados cargados");
}
async function rellenarCluster() {
  Model.knex(knexOc);
  let resultOc = await PERF_KPISVOZ_2G.query()
    .select(PERF_KPISVOZ_2G.raw("DISTINCT CLUST, REGION, SUBREGION"))
    .where(PERF_KPISVOZ_2G.raw("FECHA = TO_CHAR(SYSDATE, 'YYYYMMDD')"));
  Model.knex(knexPg);
  for (let i in resultOc) {
    let resultIdRegion = await REGION.query()
      .select("ID")
      .where("REGION", resultOc[i].REGION);

    let resultIdSubregion = await SUBREGION.query()
      .select("ID")
      .where("SUBREGION", resultOc[i].SUBREGION);

    if (typeof resultIdRegion[0] === "undefined") {
      continue;
    }

    if (typeof resultIdSubregion[0] === "undefined") {
      continue;
    }
    await CLUST.query()
      .insert({
        CLUST: resultOc[i].CLUST,
        ID_REGION: resultIdRegion[0].ID,
        ID_SUBREGION: resultIdSubregion[0].ID
      })
      .returning("ID");
  }
  //await CLUST.query().insert({ CLUST: "No Definida" }).returning("ID");
  console.log("Clusters cargados");
}
async function insertNewAlert(dataAlert) {
  Model.knex(knexPg)
  const result = await Alerts.query()
    .insert(dataAlert)
    .returning('id')
  return result
}
async function checkOccurrence(id) {
  Model.knex(knexPg)
  const result = await Alerts.query()
    .select('id', 'start_date', 'start_hour')
    .where('id', '=', id)
  return result
}
async function savePayloadByMonth(data) {
  Model.knex(knexPg)
  let result = await PayloadLatest12Month.query()
    .insert(data)
    .returning('*')
  return result
}
 async getKpiFromIdService(idService) {
   Model.knex(knexPg)
   const resultKpi = await Kpi.query()
     .select('KPI')
     .where('ID_SERVICE', idService)
   return resultKpi
 }
 async removeUser(ID) {
   Model.knex(knexPg)
   const result = await User.query()
     .delete()
     .where('ID', ID)
   return result
 }
async function saveErlang3gUntil30Days(data) {
  Model.knex(knexPg)
  let result = await Erlang3gUntil30DaysByHour.query()
    .insert(data)
    .returning('*')
  return result
}
 async getAllUsers() {
   Model.knex(knexPg)
   const result = await User.query()
     .select('*')
     .orderBy('USER_NAME', 'asc')
   return result
 }
 async findUserName(USER_NAME) {
   Model.knex(knexPg)
   const result = await User.query()
     .select('USER_NAME')
     .where('USER_NAME', USER_NAME)
   return result
 }
async function saveTotalSumPayloadByRegion(data) {
  Model.knex(knexPg)
  let result = await SumTotalPayloadMonthToDay.query()
    .insert(data)
    .returning('*')
  return result
}
module.exports = function (app){
    // setting database connection
    let env = app.get('env');
    var knex = Knex(dbConfig[env].mysqlConnection);
    Model.knex(knex);
    app.set("knex", knex);
}
//Funciones para poblar las tablas que definen las estructuras de la red
async function rellenarTecnologias() {
  Model.knex(knexPg);
  let result = await TECHNOLOGY.query()
    .insert([{ TECHNOLOGY: "2G" }, { TECHNOLOGY: "3G" }, { TECHNOLOGY: "4G" }])
    .returning("ID");
  console.log("Tecnologías cargadas");
}
Example #14
0
        return new Promise((resolve, reject) => {
                let client = knex({
                    client: 'pg',
                    connection: config.dsn,
                    searchPath: 'public'
                });
                objection.Model.knex(client);
                return client
                    .raw('SELECT version()')
                    .then((result) => {
                        let version = result.rows[0].version.match(/\d+\.\d+\.\d+/);

                        /* istanbul ignore next */
                        if (compareVersion(version, '9.5') < 0) {
                            return reject(new Error('Wrong Postgres version ' + version));
                        }

                        if (!drop) {
                            return resolve(client);
                        }

                        SCli.log(__MODULE_NAME, 'Droping pg database for ' + connectionName);

                        SCli
                            .sql(client.raw('drop schema public cascade;create schema public;'))
                            .then(() => {
                                SCli.log(__MODULE_NAME, 'Database dropped');
                                setTimeout(() => {
                                    resolve(client);
                                }, 1000);
                            });
                    }, reject);
            })
async function rellenarKpi() {
  Model.knex(knexPg);
  let result = await KPI.query()
    .insert([
      { KPI: "CCR", ID_SERVICE: 1 },
      { KPI: "ERLANG", ID_SERVICE: 1 },
      { KPI: "CSF", ID_SERVICE: 1 },
      { KPI: "DROP_CALLS", ID_SERVICE: 1 },
      { KPI: "HOF", ID_SERVICE: 1 },
      { KPI: "PCR", ID_SERVICE: 2 },
      { KPI: "PAYLOAD_TOT", ID_SERVICE: 2 },
      { KPI: "CCR", ID_SERVICE: 3 },
      { KPI: "ERLANGS", ID_SERVICE: 3 },
      { KPI: "DROP_CALLS", ID_SERVICE: 3 },
      { KPI: "PCR", ID_SERVICE: 4 },
      { KPI: "PAYLOAD_TOT", ID_SERVICE: 4 },
      { KPI: "THROUGHPUT_HSDPA", ID_SERVICE: 4 },
      { KPI: "USERS_HSDPA", ID_SERVICE: 4 },
      { KPI: "USERS_HSUPA", ID_SERVICE: 4 },
      { KPI: "DCH_USERS", ID_SERVICE: 4 },
      { KPI: "FACH_USERS", ID_SERVICE: 4 },
      { KPI: "PCR", ID_SERVICE: 5 },
      { KPI: "PAYLOAD_TOT", ID_SERVICE: 5 },
      { KPI: "THROUGHPUTDL_KBPS", ID_SERVICE: 5 },
      { KPI: "USERSDL", ID_SERVICE: 5 },
      { KPI: "USERSUL", ID_SERVICE: 5 }
    ])
    .returning("*");
  console.log("KPIs cargados");
}
 async create(dataUser) {
   Model.knex(knexPg)
   const result = await User.query()
     .insert(dataUser)
     .returning('ID')
   return result
 }
  async getTableName(idService) {
    Model.knex(knexPg)

    const resultTableName = await ObjectionNetworkServiceRepository.getTableName(
      idService
    )
    return resultTableName
  }
 async getStations(regionName) {
   Model.knex(knexOc)
   let resultOc = await PerfKpiDatos4G.query()
     .select(PerfKpiDatos4G.raw('DISTINCT ENODEB, REGION, SUBREGION, CLUST'))
     .where(PerfKpiDatos4G.raw("FECHA = TO_CHAR(SYSDATE, 'YYYYMMDD')"))
     .andWhere('REGION', regionName)
   return resultOc
 }
 async getAllSubregion() {
   Model.knex(knexPg)
   const resultSubregion = await Subregion.query().select(
     'ID',
     'SUBREGION AS REGION'
   )
   return resultSubregion
 }
 async updateUser(ID, dataUser) {
   Model.knex(knexPg)
   console.log(dataUser)
   const result = await User.query()
     .patch(dataUser)
     .where('ID', ID)
   return result
 }
  async getIdTechFromIdService(idService) {
    Model.knex(knexPg)

    const resultIdTechnology = await ObjectionNetworkServiceRepository.getIdTechFromIdService(
      idService
    )
    return resultIdTechnology
  }
Example #22
0
File: db.js Project: teikei/teikei
export default app => {
  const db = knex({
    client: Client,
    connection: app.get('postgres').connection,
    ...knexSnakeCaseMappers()
  });
  Model.knex(db)
}
 async getUsersByIdRegion(idRegion) {
   Model.knex(knexPg)
   const result = await User.query()
     .select('*')
     .where('ID_REGION', idRegion)
     .orderBy('USER_NAME', 'asc')
   return result
 }
async function deleteAlert(id_alert) {
  Model.knex(knexPg)
  await AlertsAndUsers.query()
    .delete()
    .where('id_alert', '=', id_alert)
  await Alerts.query()
    .delete()
    .where('id', '=', id_alert)
}
 async getSectorBySectorName(sectorName, date) {
   Model.knex(knexOc)
   const resultStats = await PerfKpiDatos3G.query()
     .select('NODEB', 'PCR', 'PAYLOAD')
     .where('NODEB', sectorName)
     .andWhere('FECHA', '=', date)
     .limit(1)
   return resultStats
 }
async function payload4gByRegion(region, startDate, endDate) {
  Model.knex(knexOc)
  let result = await PerfKpiDatos4G.query()
    .select(PerfKpiDatos4G.raw('SUM("PAYLOAD_TOT") as VALUE'))
    .where('FECHA', '>=', startDate)
    .andWhere('FECHA', '<=', endDate)
    .andWhere('REGION', region)
  return result
}
async function erlang3gByRegion(region, startDate, endDate) {
  Model.knex(knexOc)
  let result = await PerfKpiVoz3G.query()
    .select(PerfKpiVoz3G.raw('SUM("ERLANGS") as VALUE'))
    .where('FECHA', '>=', startDate)
    .andWhere('FECHA', '<=', endDate)
    .andWhere('REGION', region)
  return result
}
async function updateAlert(id, dateAlertUdate) {
  try {
    Model.knex(knexPg)
    const result = await Alerts.query()
      .update(dateAlertUdate)
      .where('id', '=', id)
  } catch (e) {
    console.log(e)
  }
}
  async getStatisticsSubregionCluster(
    sinceTheDate,
    untilTheDate,
    subregionName,
    kpiName
  ) {
    if (subregionName === 'Valles Del Tuy') {
      subregionName = '%Valles %'
    }
    Model.knex(knexOc)
    let querySelect =
      "(TO_DATE(FECHA || HORA, 'YYYYMMDDHH24') - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) *24 * 60 * 60 * 1000 as FECHA, AVG(" +
      kpiName +
      ') as VALOR'

    if (kpiName === 'PAYLOAD_TOT') {
      //ELIMINAR LA SIGUIENTE LINEA AL CORREGIR EL NOMBRE DEL KPI EN LA BASE DE DATOS
      kpiName = 'PAYLOAD_TOTAL'
      querySelect =
        "(TO_DATE(FECHA || HORA, 'YYYYMMDDHH24') - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) *24 * 60 * 60 * 1000 as FECHA, SUM(" +
        kpiName +
        ') as VALOR'
    }

    if (kpiName === 'ERLANG' || kpiName === 'ERLANGS')
      querySelect =
        "(TO_DATE(FECHA || HORA, 'YYYYMMDDHH24') - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) *24 * 60 * 60 * 1000 as FECHA, SUM(" +
        kpiName +
        ') as VALOR'

    if (
      kpiName === 'USERSDL' ||
      kpiName === 'USERSUL' ||
      kpiName === 'USERS_HSDPA' ||
      kpiName === 'USERS_HSUPA'
    ) {
      //ELIMINAR LAS SIGUIENTES CUATRO LINEAS CUANDO SE CORRIJA EL NOMBRE DE LOS ATRIBUTOS EN LA BASE DE DATOS DE PERFORMANCE
      if (kpiName === 'USERS_HSDPA') kpiName = 'MAX_USER_HSDPA'
      if (kpiName === 'USERS_HSUPA') kpiName = 'MAX_USER_HSUPA'
      querySelect =
        "(TO_DATE(FECHA || HORA, 'YYYYMMDDHH24') - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) *24 * 60 * 60 * 1000 as FECHA, SUM(" +
        kpiName +
        ') as VALOR'
    }

    const resultStats = await PerfKpiDatos3GCluster.query()
      .select(PerfKpiDatos3GCluster.raw(querySelect))
      .where('FECHA', '>=', sinceTheDate)
      .andWhere('FECHA', '<=', untilTheDate)
      .andWhere('SUBREGION', 'like', subregionName)
      .groupBy('FECHA', 'HORA')
      .orderBy('FECHA', 'asc')
    return resultStats
  }
async function rellenarSubregion() {
  Model.knex(knexOc);
  let resultOc = await PERF_KPISVOZ_2G.query()
    .select(PERF_KPISVOZ_2G.raw("DISTINCT SUBREGION"))
    .where(PERF_KPISVOZ_2G.raw("FECHA = TO_CHAR(SYSDATE, 'YYYYMMDD')"));
  Model.knex(knexPg);
  for (let i in resultOc) {
    if (resultOc[i].SUBREGION == null) {
      // await SUBREGION.query()
      // .insert({ SUBREGION: 'No definida' })
      // .returning("ID");
      continue;
    }
    await SUBREGION.query()
      .insert({ SUBREGION: resultOc[i].SUBREGION })
      .returning("ID");
  }
  //await SUBREGION.query().insert({ SUBREGION: "No Definida" }).returning("ID");
  console.log("Subregiones cargadas");
}