DpackGetData/util/timerQuery.js

var oracleModel = require("../model/oracle.js");
var sqlServer = require("../util/db_sqlserver.js");

var sql = require("mssql");

var util = require("../util/util.js");
var log = require("../util/log.js");

var cron = require("cron");
var getQueryJob = cron.job("* * * * * *");

var startMorning = cron.job("* * * * * *");
var endMorning = cron.job("* * * * * *");
var startNight = cron.job("* * * * * *");
var endNight = cron.job("* * * * * *");
var checkStartJob = cron.job("* * * * * *");

var collectionInsertJob = cron.job("* * * * * *");

var oracleInsertJob = cron.job("* * * * * *");

var moment = require("moment");

function scheduleData() {
  var configjs = util.getConfig();
  startMorning.stop();
  endMorning.stop();
  startNight.stop();
  endNight.stop();
  //startMorning.stop();

  var startMorningTime = configjs.config_time.start_morning;
  startMorning = cron.job(
    `00 ${startMorningTime.substr(3, 2)} ${startMorningTime.substr(
      0,
      2
    )} * * *`,
    function () {
      var process = util.getProcess();
      process.is_running = "FALSE";
      util.saveProcess(process);
      getQueryJob.stop();
      collectionInsertJob.stop();
      oracleInsertJob.stop();
      util.infoNotif(
        "Waktu Good Morning",
        "Job akan dimatikan untuk sementara"
      );
    },
    null,
    true
  );

  var endMorningTime = configjs.config_time.end_morning;
  endMorning = cron.job(
    `00 ${endMorningTime.substr(3, 2)} ${endMorningTime.substr(0, 2)} * * *`,
    function () {
      var process = util.getProcess();
      if (process.is_running != "TRUE") {
        process.is_running = "TRUE";
        process.processing.process_getQuery = "TRUE";
        process.processing.process_insertToCollection = "TRUE";
        process.processing.process_insertToOracle = "TRUE";

        util.saveConfig(configjs);
        getQueryJob.start();
        collectionInsertJob.start();
        oracleInsertJob.start();
        util.infoNotif("Good Morning Berakhir", "Job akan kembali dihidupkan");
      }
    },
    null,
    true
  );

  var startNightTime = configjs.config_time.start_night;
  startNight = cron.job(
    `00 ${startNightTime.substr(3, 2)} ${startNightTime.substr(0, 2)} * * *`,
    function () {
      var process = util.getProcess();
      process.is_running = "FALSE";
      util.saveProcess(process);
      getQueryJob.stop();
      collectionInsertJob.stop();
      oracleInsertJob.stop();
      util.infoNotif("Waktu Good Bye", "Job akan dimatikan untuk sementara");
    },
    null,
    true
  );

  var endNightTime = configjs.config_time.end_night;
  endMorning = cron.job(
    `00 ${endNightTime.substr(3, 2)} ${endNightTime.substr(0, 2)} * * *`,
    function () {
      var process = util.getProcess();
      if (process.is_running != "TRUE") {
        process.is_running = "TRUE";
        process.auto_start = "TRUE";
        process.processing.process_getQuery = "TRUE";
        process.processing.process_insertToCollection = "TRUE";
        process.processing.process_insertToOracle = "TRUE";

        util.saveProcess(process);
        getQueryJob.start();
        collectionInsertJob.start();
        oracleInsertJob.start();
        util.infoNotif("Good Bye Berakhir", "Job akan kembali dihidupkan");
      }
    },
    null,
    true
  );
}

module.exports.stopAllJob = async function stopAllJob() {
  startMorning.stop();
  endMorning.stop();
  startNight.stop();
  endNight.stop();
  getQueryJob.stop();
  collectionInsertJob.stop();
  checkStartJob.stop();
  oracleInsertJob.stop();

  return;
};

module.exports.checkJobIsRunning = function checkJobIsRunning() {
  var dataReturn = {
    getQuery: getQueryJob.running,
    collectionInsert: collectionInsertJob.running,
    oracleInsert: oracleInsertJob.running,
  };
  return dataReturn;
};

module.exports.checkStart = function checkStart() {
  checkStartJob.stop();
  var self = this;
  checkStartJob = cron.job("0 */5 * * * *", async function () {
    log.createLogStart(2, "Check Start");
    var process = util.getProcess();
    if (process.is_running == "TRUE") {
      if (!getQueryJob.running) {
        self.getQuery();
        log.createLogStart(2, "Running Get Query");
        process.processing.process_getQuery = "TRUE";
      }
      if (!collectionInsertJob.running) {
        self.insertToCollection();
        log.createLogStart(2, "Running Collection Insert");
        process.processing.process_insertToCollection = "TRUE";
      }
      if (!oracleInsertJob.running) {
        self.insertToOracle();
        log.createLogStart(2, "Running Insert To Oracle");
        process.processing.process_insertToOracle = "TRUE";
      }
      util.saveProcess(process);
    }
  });
  checkStartJob.start();
  return;
};

module.exports.startAllServices = async function startAllServices() {
  var process = util.getProcess();
  process.is_running = "TRUE";
  process.processing.process_getQuery = "TRUE";
  process.processing.process_insertToCollection = "TRUE";
  process.processing.process_insertToOracle = "TRUE";
  util.saveProcess(process);

  await this.checkStart();
  await this.getQuery();
  await this.insertToCollection();
  await this.insertToOracle();
  return;
};

module.exports.getQuery = async function getQuery() {
  var process = util.getProcess();
  process.processing.process_getQuery = "TRUE";
  util.saveProcess(process);
  var sqlCollection = await sqlServer.connectCollection();

  var requestLastDateAvailability = sqlCollection.request();
  var queryLastDateAvailability = `SELECT COLUMN_NAME
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'UPLOAD_DATA_DB_TAB' AND COLUMN_NAME='LAST_DATE'
		IF @@ROWCOUNT=0
			ALTER TABLE dbo.UPLOAD_DATA_DB_TAB ADD LAST_DATE datetime`;

  var resultLastDateAvailability = await requestLastDateAvailability.query(
    queryLastDateAvailability
  );

  if (configjs.firstrun == "TRUE") {
    var date6 = moment()
      .startOf("day")
      .add(6, "hours")
      .format("YYYY-MM-DD HH:mm:ss.SSS");
    var reqUpdateLastDate6 = sqlCollection.request();
    var queryUpdateLastDate6 = `update dbo.UPLOAD_DATA_DB_TAB SET LAST_DATE = Cast('${date6}' as datetime)`;
    var resultUpdateLastDate6 = await reqUpdateLastDate6.query(
      queryUpdateLastDate6
    );
    configjs = util.getConfig();
    configjs.firstrun = "FALSE";
    util.saveConfig(configjs);
  }

  getQueryJob.stop();
  scheduleData();

  getQueryJob = cron.job(
    `15 */${parseInt(configjs.interval.getquery_interval)} * * * *`,
    async function () {
      if (util.getProcess().processing.process_getQuery == "TRUE") {
        process = util.getProcess();
        process.processing.process_getQuery = "FALSE";
        util.saveProcess(process);
        try {
          var oracleSelect = await oracleModel.oracleQueryUploadData();
          if (oracleSelect["STATUS"] == 0) {
            getQueryJob.stop();
          } else {
            var data = oracleSelect["DATA"];
            const requestDelOldDetail = sqlCollection.request();
            var queryDelOldDetail = `delete from dbo.UPLOAD_DATA_DB_DET_TAB`;
            var resultDelOldDetail = await requestDelOldDetail.query(
              queryDelOldDetail
            );

            await sqlCollection;
            for (var z = 0; z < data.length; z++) {
              try {
                const request = sqlCollection.request();
                request.input("COMPANY", data[z].COMPANY);
                request.input("ID", data[z].ID);
                request.input("ENABLED", data[z].ENABLED);
                request.input("TEST_MODE", data[z].TEST_MODE);
                request.input("AUTO_MODE", data[z].AUTO_MODE);
                var queryInsertUploadData;

                if (data[z].LAST_DATE != null) {
                  var lastDate = data[z].LAST_DATE;
                  request.input("LAST_DATE", lastDate);

                  queryInsertUploadData = `
								UPDATE UPLOAD_DATA_DB_TAB SET COMPANY = @COMPANY, ENABLED = @ENABLED,
								TEST_MODE = @TEST_MODE, AUTO_MODE = @AUTO_MODE, LAST_DATE = @LAST_DATE WHERE ID = @ID
								IF @@ROWCOUNT=0
								   INSERT INTO UPLOAD_DATA_DB_TAB(COMPANY,ID,ENABLED,TEST_MODE,AUTO_MODE,LAST_DATE) values
								   (@COMPANY,@ID,@ENABLED,@TEST_MODE,@AUTO_MODE,@LAST_DATE);`;

                  var resultUpdateLastDate = await oracleModel.oracleUpdateLastDate(
                    data[z].ID
                  );
                } else {
                  queryInsertUploadData = `
								UPDATE UPLOAD_DATA_DB_TAB SET COMPANY = @COMPANY, ENABLED = @ENABLED,
								TEST_MODE = @TEST_MODE, AUTO_MODE = @AUTO_MODE WHERE ID = @ID
								IF @@ROWCOUNT=0
								   INSERT INTO UPLOAD_DATA_DB_TAB(COMPANY,ID,ENABLED,TEST_MODE,AUTO_MODE) values
								   (@COMPANY,@ID,@ENABLED,@TEST_MODE,@AUTO_MODE);`;
                }

                //Insert to Table
                const result = await request.query(queryInsertUploadData);
                var oracleSelectDetail = await oracleModel.oracleQueryUploadDataDet(
                  data[z].ID
                );
                if (oracleSelectDetail["STATUS"] == 0) {
                  throwNotif(
                    "Database Oracle Error",
                    oracleSelectDetail["DATA"]
                  );
                  process = util.getProcess();
                  process.processing.process_getQuery = "TRUE";
                  util.saveProcess(process);
                } else {
                  try {
                    var dataDetail = oracleSelectDetail["DATA"];
                    for (var i = 0; i < dataDetail.length; i++) {
                      const requestDet = sqlCollection.request();
                      requestDet.input("COMPANY", dataDetail[i]["COMPANY"]);
                      requestDet.input("ID", dataDetail[i]["ID"]);
                      requestDet.input("NO", sql.Int, dataDetail[i]["NO"]);
                      requestDet.input(
                        "NO_ORDER",
                        sql.Int,
                        dataDetail[i]["NO_ORDER"]
                      );
                      requestDet.input(
                        "SOURCE_QUERY",
                        dataDetail[i]["SOURCE_QUERY"]
                      );
                      requestDet.input(
                        "DESC_PACKAGE",
                        dataDetail[i]["DESC_PACKAGE"]
                      );
                      requestDet.input("ENABLED", dataDetail[i]["ENABLED"]);
                      requestDet.input(
                        "DEST_PACKAGE_TYPE",
                        dataDetail[i]["DEST_PACKAGE_TYPE"]
                      );
                      var queryInsertUploadDataDetail = `
											UPDATE UPLOAD_DATA_DB_DET_TAB SET COMPANY = @COMPANY, NO_ORDER = @NO_ORDER,
											SOURCE_QUERY = @SOURCE_QUERY ,DESC_PACKAGE = @DESC_PACKAGE,ENABLED = @ENABLED,
											DEST_PACKAGE_TYPE = @DEST_PACKAGE_TYPE WHERE ID = @ID AND NO = @NO
											IF @@ROWCOUNT=0
											   INSERT INTO UPLOAD_DATA_DB_DET_TAB(COMPANY,ID,NO,NO_ORDER,SOURCE_QUERY,DESC_PACKAGE,ENABLED,DEST_PACKAGE_TYPE) 
											   values (@COMPANY,@ID,@NO,@NO_ORDER,@SOURCE_QUERY,@DESC_PACKAGE,@ENABLED,@DEST_PACKAGE_TYPE);`;
                      const resultDetail = await requestDet.query(
                        queryInsertUploadDataDetail
                      );
                    }
                  } catch (e) {
                    process = util.getProcess();
                    process.processing.process_getQuery = "TRUE";
                    util.saveProcess(process);
                    getQueryJob.stop();
                  }
                }
              } catch (e) {
                process = util.getProcess();
                process.processing.process_getQuery = "TRUE";
                util.saveProcess(process);
                getQueryJob.stop();
              }
            }
            process = util.getProcess();
            process.processing.process_getQuery = "TRUE";
            util.saveProcess(process);
            var now = moment().format("YYYY-MM-DD HH:mm:ss.SSS");
            console.log("1: " + now);
          }
        } catch (e) {
          getQueryJob.stop();
          console.log(e.message);
        }
      }
    },
    null,
    true,
    null,
    null,
    true
  );
  getQueryJob.start();
  return;
};

module.exports.insertToCollection = async function insertToCollection() {
  var sqlDpack = await sqlServer.connectDPACK();
  var sqlCollection = await sqlServer.connectCollection();
  var configjs = util.getConfig();
  var process = util.getProcess();
  process.processing.process_insertToCollection = "TRUE";
  util.saveProcess(process);
  var rowInserted = 0;
  var errorLog = 0;

  collectionInsertJob.stop();
  collectionInsertJob = cron.job(
    `45 */${parseInt(configjs.interval.collection_interval)} * * * *`,
    async function () {
      try {
        if (util.getProcess().processing.process_insertToCollection == "TRUE") {
          process = util.getProcess();
          process.processing.process_insertToCollection = "FALSE";
          util.saveProcess(process);

          await sqlDpack;
          await sqlCollection;
          var requestSelectParentCollection = sqlCollection.request();
          var querySelectParentCollection = `select ID,LAST_DATE from dbo.UPLOAD_DATA_DB_TAB`;
          var resultSelectParentCollection = await requestSelectParentCollection.query(
            querySelectParentCollection
          );
          var resultID = resultSelectParentCollection.recordset;

          for (var z = 0; z < resultID.length; z++) {
            var requestSelectDetailCollection = sqlCollection.request();
            var querySelectDetailCollection = `select source_query from dbo.UPLOAD_DATA_DB_DET_TAB where ID = '${resultID[z].ID}'`;
            var resultSelectDetailCollection = await requestSelectDetailCollection.query(
              querySelectDetailCollection
            );
            var resultSourceQuery = resultSelectDetailCollection.recordset;

            if (process.processing.process_getQuery != "FALSE") {
              var currentTime = moment().format("YYYY-MM-DD HH:mm:ss.SSS");
              var requestUpdateLastDate = sqlCollection.request();
              var queryUpdateLastDate = `update dbo.UPLOAD_DATA_DB_TAB set LAST_DATE = Cast('${currentTime}' as datetime) where ID = '${resultID[z].ID}'`;
              var resultUpdateLastDate = requestUpdateLastDate.query(
                queryUpdateLastDate
              );
            }
            for (var k = 0; k < resultSourceQuery.length; k++) {
              var sourceQuery = await util.regexQuery(
                resultSourceQuery[k].source_query,
                resultID[z].LAST_DATE
              );

              try {
                var requestQuery = sqlDpack.request();
                var result = await requestQuery.query(sourceQuery);

                var arrayColumn = result.recordset.columns;
                var columnLength = Object.keys(arrayColumn).length;
                var resultRow = result.recordset;

                for (var i = 0; i < resultRow.length; i++) {
                  var processingRow = resultRow[i];

                  var varInsert = "";
                  var varBind = "";
                  var string = 1;
                  var int = 1;
                  var date = 1;
                  var count = 0;

                  var requestInsertCollection = sqlCollection.request();

                  for (key in arrayColumn) {
                    count += 1;
                    var checktype;
                    checktype = await sqlServer.checkDataType(
                      arrayColumn[key].type
                    );
                    if (count == 1) {
                      requestInsertCollection.input(
                        "collection_name",
                        processingRow[key]
                      );
                      varInsert += `collection_name,flag_deleted`;
                      varBind += `@collection_name,'FALSE'`;
                    } else {
                      if (checktype == 0) {
                        varInsert += `s${string}`;
                        varBind += `@s${string}`;
                        requestInsertCollection.input(
                          `s${string}`,
                          sql.VarChar,
                          processingRow[key]
                        );
                        string += 1;
                      } else if (checktype == 1) {
                        varInsert += `n${int}`;
                        varBind += `@n${int}`;
                        requestInsertCollection.input(
                          `n${int}`,
                          sql.Int,
                          processingRow[key]
                        );
                        int += 1;
                      } else {
                        varInsert += `d${date}`;
                        varBind += `@d${date}`;
                        requestInsertCollection.input(
                          `d${date}`,
                          sql.DateTime,
                          processingRow[key]
                        );
                        date += 1;
                      }
                    }
                    if (count != columnLength) {
                      varInsert += `,`;
                      varBind += `,`;
                    }
                  }

                  var queryInsertCollection = `insert into TEMP_DATA_TAB(${varInsert}) values(${varBind})`;
                  var resultInsertCollection = await requestInsertCollection.query(
                    queryInsertCollection
                  );
                  rowInserted += resultInsertCollection.rowsAffected[0];
                  if (isNaN(resultInsertCollection.rowsAffected[0])) {
                    errorLog = 1;
                  }
                }
              } catch (e) {
                var process = util.getProcess();
                process.processing.process_insertToCollection = "TRUE";
                util.saveProcess(process);
                collectionInsertJob.stop();
              }
            }

            if (errorLog == 1) {
              var currentTime = moment().format("YYYY-MM-DD HH:mm:ss.SSS");
              var requestUpdateLastDate = sqlCollection.request();
              var queryUpdateLastDate = `update dbo.UPLOAD_DATA_DB_TAB set LAST_DATE = ${resultID[z].LAST_DATE} where ID = '${resultID[z].ID}'`;
              var resultUpdateLastDate = requestUpdateLastDate.query(
                queryUpdateLastDate
              );
              var process = util.getProcess();
              process.processing.process_insertToCollection = "TRUE";
              util.saveProcess(process);
              errorLog = 0;
            }
          }
          var now = moment().format("YYYY-MM-DD HH:mm:ss.SSS");
          var process = util.getProcess();
          process.processing.process_insertToCollection = "TRUE";
          util.saveProcess(process);
          console.log("2 : " + now);
          console.log("ROW INSERTED : " + rowInserted);
          rowInserted = 0;
        }
      } catch (e) {
        log.createLog(0, e.message);
        var process = util.getProcess();
        process.processing.process_insertToCollection = "TRUE";
        util.saveProcess(process);
      }
    }
  );
  collectionInsertJob.start();
  return;
};

module.exports.insertToOracle = async function insertToOracle() {
  var sqlCollection = await sqlServer.connectCollection();
  var configjs = util.getConfig();
  var process = util.getProcess();
  process.processing.process_insertToOracle = "TRUE";
  util.saveProcess(process);
  oracleInsertJob.stop();

  oracleInsertJob = cron.job(
    `30 */${parseInt(configjs.interval.oracle_interval)} * * * *`,
    async function () {
      if (util.getProcess().processing.process_insertToOracle == "TRUE") {
        var process = util.getProcess();
        process.processing.process_insertToOracle = "FALSE";
        util.saveProcess(process);

        await sqlCollection;
        try {
          var requestSelectTempCollection = sqlCollection.request();
          var querySelectTempCollection = `SELECT TOP 100000 COLLECTION_NAME, S1, S2, S4, S3, S5, S6, S7, S8, S9, S10, S11, S12, S13, S14, S15, S19, S18, S17, S16, S20, S21, S22, S23, S24, S25, S26, S27, S28, S29, S30, S31, S32, S33, S34, S35, S36, 
                  S37, S38, S39, S40, S41, S42, S43, S44, S45, S46, S47, S48, S50, S49, N1, N2, N3, N4, N5, N6, N7, N8, N9, N10, N11, N12, N13, N14, N15, N16, N17, N18, N19, N20, N21, N22, N23, N24, N25, N26, 
                  N27, N28, N29, N30, N31, N32, N33, N34, N35, N36, N37, N38, N39, N40, N41, N42, N43, N44, N45, N46, N47, N48, N49, N50, D1, D2, D3, D4, D5, D6, D7, D8, D9, D10, D11, D12, D13, D14, D15, 
                  D16, D17, D18, D19, D20, D21, D22, D23, D24, D25, D26, D27, D28, D29, D30,SEQ_ID
					FROM  dbo.TEMP_DATA_TAB WHERE FLAG_DELETED = 'FALSE'`;
          var resultSelectTempCollection = await requestSelectTempCollection.query(
            querySelectTempCollection
          );
          var resultRow = resultSelectTempCollection.recordset;

          if (resultSelectTempCollection.recordset.length != 0) {
            var data = await oracleModel.oracleInsertCollection(
              resultSelectTempCollection.recordset
            );
          }
          process = util.getProcess();
          process.processing.process_insertToOracle = "TRUE";
          util.saveProcess(process);
          var now = moment().format("YYYY-MM-DD HH:mm:ss.SSS");
        } catch (e) {
          console.log(e.message);
          process = util.getProcess();
          process.processing.process_insertToOracle = "TRUE";
          util.saveProcess(process);
          oracleInsertJob.stop();
        }
      }
    }
  );
  oracleInsertJob.start();
  return;
  //}
};