Unit-api/model/oracleModel.js

var oracleFunc = require("../util/db_oracle.js");
const oracledb = require("oracledb");
const fs = require("fs");
var moment = require("moment");

var owner = "TBS";

async function getDataCabangByUser(company, user) {
    let conn;
    try {
        conn = await oracleFunc.getConnection();
        var query = `
            SELECT NAMA_CABANG AS DISPLAY_VALUE, CABANG_ID AS RETURN_VALUE
                FROM ${owner}.CABANG
                WHERE COMPANY = :COMPANY
                AND ${owner}.User_Per_Cabang_API.Is_User_Available(company, cabang_id, :USERNAME) = 1
                ORDER BY NAMA_CABANG
        `;

        var params = { COMPANY: company, USERNAME: user };
        console.log(params);
        var result = await oracleFunc.getQueryData(conn, query, params);

        await oracleFunc.doRelease(conn);
        return { STATUS: 1, DATA: result };
    } catch (e) {
        await oracleFunc.doRelease(conn);
        return { STATUS: 0, DATA: e };
    }
}

async function getDataUser(user = "", pass = "") {
    let conn;
    try {
        conn = await oracleFunc.getConnection();

        var checkView2 = `
            select ${owner}.UTILS_API.Is_View_Available('STOCK_TAKING_UNIT_MOB_APP', :USERNAME) LOGIN_STATUS from dual
        `;
        var paramView = { USERNAME: user };
        var resultView = await oracleFunc.getQueryData(
            conn,
            checkView2,
            paramView
        );
        if (resultView[0]["LOGIN_STATUS"] == "FALSE")
            throw "Username or password incorrect !";

        var checkQuery = `
            select ${owner}.Mobile_APP_Utils_API.Sales_Mobile_Auth(:USERNAME, :PASSWORD) data from dual
        `;
        var params = { USERNAME: user, PASSWORD: pass };
        var result = await oracleFunc.getQueryData(conn, checkQuery, params);
        var dataUser = JSON.parse(result[0]["DATA"]);
        if (dataUser["status"] == "ERR") throw dataUser["info"];

        await oracleFunc.doRelease(conn);

        return {
            STATUS: 1,
            DATA: {
                COMPANY: dataUser["company"],
                USER: dataUser["login_db_user"],
            },
        };
    } catch (e) {
        await oracleFunc.doRelease(conn);
        return { STATUS: 0, DATA: e };
    }
}

async function getDataCabang(company = "%", cabangId = "%") {
    var query = `select 
                    company,
                    cabang_id,
                    nama_cabang,
                    latitude,
                    longitude
                 from ${owner}.cabang
                 where company like :COMPANY
                 and cabang_id like :CABANG_ID`;
    var params = { COMPANY: company, CABANG_ID: cabangId };
    var result = await oracleFunc.getData(query, params);
    return result;
}

async function getStateStockTaking(company, stockTakingId) {
    let conn;
    try {
        conn = await oracleFunc.getConnection();
        var query = `
            select ${owner}.Stock_Taking_Unit_API.Get_Status_Stock_Taking(
               company_                => :COMPANY,
               stock_taking_unit_id_   => :STOCK_TAKING_UNIT_ID) state from dual
        `;
        var params = { COMPANY: company, STOCK_TAKING_UNIT_ID: stockTakingId };
        var result = await oracleFunc.getQueryData(conn, query, params);

        await oracleFunc.doRelease(conn);
        return { STATUS: 1, DATA: result[0] };
    } catch (e) {
        await oracleFunc.doRelease(conn);
        return { STATUS: 0, DATA: e };
    }
}

async function getActiveStockTakingByCabang(company, cabangId) {
    let conn;
    try {
        conn = await oracleFunc.getConnection();
        var query = `
            SELECT * 
            FROM (
                        SELECT
                            stock_taking_unit_id,
                            state
                        from ${owner}.stock_taking_unit
                        where company = :COMPANY
                        and cabang_id = :CABANG_ID
                        and state IN ('Open','Submitted')
                        order by tgl_start desc
                        )
            WHERE rownum = 1
        `;

        var paramsQuery = { COMPANY: company, CABANG_ID: cabangId };
        var result = await oracleFunc.getQueryData(conn, query, paramsQuery);
        var a = result[0];

        await oracleFunc.doRelease(conn);
        return a;
    } catch (e) {
        await oracleFunc.doRelease(conn);
        throw e;
    }
}

async function getDataUnit(conn, company, cabangId) {
    var query = `SELECT
                    u.company,
                    u.cabang_id,
                    u.mesin,
                    u.kode,
                    u.rangka,
                    u.tipe,
                    u.warna,
                    u.tahun,
                    u.state,
                    c.channel_name channel
                FROM ${owner}.unit_baru u
                left join ${owner}.CHANNEL c on
                u.channel_id = c.channel_id
                WHERE u.company = :COMPANY
                AND u.cabang_id = :CABANG_ID
                AND u.state IN ('OnTheWay','Ready','OnChannel','InRepair')
                ORDER BY u.tipe
    `;

    var params = { COMPANY: company, CABANG_ID: cabangId };
    var result = await oracleFunc.getQueryData(conn, query, params);
    return result;
}

async function submitStockTaking(company, stockTakingId) {
    let conn;
    try {
        conn = await oracleFunc.getConnection();
        var query = `
            BEGIN
                ${owner}.STOCK_TAKING_UNIT_API.Call_Submit(
                   company_                => :COMPANY,
                   stock_taking_unit_id_   => :STOCK_TAKING_UNIT_ID);
            END;
        `;

        var params = { COMPANY: company, STOCK_TAKING_UNIT_ID: stockTakingId };
        console.log(params);
        await oracleFunc.runQueryData(conn, query, params);
        await oracleFunc.doCommit(conn);
        await oracleFunc.doRelease(conn);

        return { STATUS: 1, DATA: "Stock Taking Submit Completed " };
    } catch (e) {
        await oracleFunc.doRelease(conn);
        return { STATUS: 0, DATA: e };
    }
}

async function prepareStockCollection(company = "", cabangId = "") {
    let conn;
    try {
        conn = await oracleFunc.getConnection();
        var queryStockUnit = `
            BEGIN
                ${owner}.Stock_Taking_Unit_API.Create_Update_Record(
                   company_                   => :COMPANY,
                   stock_taking_unit_id_      => :STOCK_TAKING_UNIT_ID,
                   tgl_stock_taking_          => TO_DATE(:TGL_STOCK_TAKING,'DD-MM-YYYY'),
                   tgl_start_                 => TO_DATE(:TGL_START,'DD-MM-YYYY HH24:MI:SS'),
                   tgl_selesai_               => TO_DATE(:TGL_SELESAI,'DD-MM-YYYY HH24:MI:SS'),
                   cabang_id_                 => :CABANG_ID);
            END;`;

        var paramStockUnit = {
            COMPANY: company,
            STOCK_TAKING_UNIT_ID: { dir: oracledb.BIND_OUT },
            TGL_STOCK_TAKING: moment().format("DD-MM-YYYY"),
            TGL_START: moment().format("DD-MM-YYYY HH:mm:ss"),
            TGL_SELESAI: null,
            CABANG_ID: cabangId,
        };

        var resultStockUnit = await oracleFunc.runQueryData(
            conn,
            queryStockUnit,
            paramStockUnit
        );
        var stockTakingUnitId =
            resultStockUnit["outBinds"]["STOCK_TAKING_UNIT_ID"];
        var jenisFoto = [
            { NO_URUT: 1, JENIS: "NOMOR RANGKA" },
            { NO_URUT: 2, JENIS: "TAMPAK SAMPING" },
        ];

        var paramsUnitDet = [];
        var paramsUnitDetPic = [];
        var dataUnitCabang = await getDataUnit(conn, company, cabangId);
        for (var i = 0; i < dataUnitCabang.length; i++) {
            var temp = {
                COMPANY: company,
                STOCK_TAKING_ID: stockTakingUnitId,
                MESIN: dataUnitCabang[i]["MESIN"],
                FLAG_REJECT: "FALSE",
                KETERANGAN: null,
            };
            paramsUnitDet.push(temp);
            for (var j = 0; j < jenisFoto.length; j++) {
                var tempPic = {
                    COMPANY: company,
                    STOCK_TAKING_ID: stockTakingUnitId,
                    MESIN: dataUnitCabang[i]["MESIN"],
                    NO_URUT: jenisFoto[j]["NO_URUT"],
                    JENIS: jenisFoto[j]["JENIS"],
                    LATITUDE: null,
                    LONGITUDE: null,
                    LOB_ID: null,
                };
                paramsUnitDetPic.push(tempPic);
            }
        }

        var queryStockUnitDet = `
            BEGIN
                ${owner}.STOCK_TAKING_UNIT_DET_API.Create_Update_Record(
                   company_                => :COMPANY,
                   stock_taking_unit_id_   => :STOCK_TAKING_ID,
                   mesin_                  => :MESIN,
                   flag_reject_            => :FLAG_REJECT,
                   keterangan_             => :KETERANGAN);
            END;
        `;

        await oracleFunc.runQueryMany(conn, queryStockUnitDet, paramsUnitDet);

        var queryStockUnitPic = `
            BEGIN
                ${owner}.STOCK_TAKING_UNIT_DET_PIC_API.Create_Update_Record( 
                   company_                => :COMPANY, 
                   stock_taking_unit_id_   => :STOCK_TAKING_ID, 
                   mesin_                  => :MESIN, 
                   no_urut_                => :NO_URUT, 
                   jenis_                  => :JENIS, 
                   latitude_               => :LATITUDE, 
                   longitude_              => :LONGITUDE, 
                   lob_id_                 => :LOB_ID   
                  ); 
            END;
        `;

        await oracleFunc.runQueryMany(
            conn,
            queryStockUnitPic,
            paramsUnitDetPic
        );

        await oracleFunc.doCommit(conn);
        await oracleFunc.doRelease(conn);
        return {
            stockTakingUnitId: stockTakingUnitId,
            dataUnitCabang: dataUnitCabang,
            dataUnitPic: paramsUnitDetPic,
            jenisFoto: jenisFoto,
        };
    } catch (e) {
        oracleFunc.doRollBack(conn);
        throw e;
    }
}

async function getActiveStockCollection(
    company = "",
    cabangId = "",
    stockTakingUnitId = ""
) {
    let conn;
    try {
        conn = await oracleFunc.getConnection();
        var queryDataUnit = `
            select 
                a.company,
                a.stock_taking_unit_id,
                a.mesin,
                b.kode,
                b.rangka,
                b.tipe,
                b.warna,
                b.tahun,
                b.state,
                c.channel_name channel 
            from ${owner}.stock_taking_unit_det a LEFT JOIN ${owner}.unit_baru b
            ON a.company = b.company
            and a.mesin = b.mesin
            and b.cabang_id = :CABANG_ID
            left join ${owner}.CHANNEL c on
            b.channel_id = c.channel_id
            where a.company = :COMPANY
            and a.stock_taking_unit_id = :STOCK_TAKING_UNIT_ID
            and a.mesin NOT IN (select mesin from ${owner}.stock_taking_unit_det_pic c
                where c.COMPANY = :COMPANY
                AND c.stock_taking_unit_id = :STOCK_TAKING_UNIT_ID
                AND lob_id IS NOT NULL
            )
            ORDER by tipe            
        `;

        var paramsDataUnit = {
            COMPANY: company,
            CABANG_ID: cabangId,
            STOCK_TAKING_UNIT_ID: stockTakingUnitId,
        };
        var dataUnit = await oracleFunc.getQueryData(
            conn,
            queryDataUnit,
            paramsDataUnit
        );

        var queryDataUnitPic = `
            SELECT
                Company,
                stock_taking_unit_id,
                mesin,
                no_urut,
                jenis,
                latitude,
                longitude,
                lob_id
            from ${owner}.stock_taking_unit_det_pic 
            where company = :COMPANY
            and stock_taking_unit_id = :STOCK_TAKING_UNIT_ID
            and lob_id IS NULL

        `;

        var paramsDataUnitPic = {
            COMPANY: company,
            STOCK_TAKING_UNIT_ID: stockTakingUnitId,
        };

        var dataUnitPic = await oracleFunc.getQueryData(
            conn,
            queryDataUnitPic,
            paramsDataUnitPic
        );

        var jenisFoto = [
            { NO_URUT: 1, JENIS: "NOMOR RANGKA" },
            { NO_URUT: 2, JENIS: "TAMPAK SAMPING" },
        ];

        await oracleFunc.doRelease(conn);

        return {
            stockTakingUnitId: stockTakingUnitId,
            dataUnitCabang: dataUnit,
            dataUnitPic: dataUnitPic,
            jenisFoto: jenisFoto,
        };
    } catch (e) {
        await oracleFunc.doRelease(conn);
        throw e;
    }
}

async function insertStockUnitData(stockTakingData, dataUnit, dataFoto,userId) {
    let conn;
    try {
        conn = await oracleFunc.getConnection();
        var queryUpdateTglSelesai = `
            BEGIN
                ${owner}.STOCK_TAKING_UNIT_API.Update_Tgl_Selesai(
                   company_                => :COMPANY,
                   stock_taking_unit_id_   => :STOCK_TAKING_UNIT_ID,
                   tgl_selesai_            => TO_DATE(:TGL_SELESAI,'DD-MM-YYYY HH24:MI:SS'));
            END;
        `;
        var paramsUpdateTglSelesai = {
            COMPANY: stockTakingData["company"],
            STOCK_TAKING_UNIT_ID: stockTakingData["stockTakingUnitId"],
            TGL_SELESAI: moment().format("DD-MM-YYYY HH:mm:ss"),
        };

        await oracleFunc.runQueryData(
            conn,
            queryUpdateTglSelesai,
            paramsUpdateTglSelesai
        );

        if (dataUnit.length > 0) {
            var queryUpdateKeteranganDet = `
                BEGIN
                    ${owner}.STOCK_TAKING_UNIT_DET_API.Update_Keterangan(
                       company_                   => :COMPANY,
                       stock_taking_unit_id_      => :STOCK_TAKING_UNIT_ID,
                       mesin_                     => :MESIN,
                       keterangan_                => :KETERANGAN);
                END;
            `;
            var paramsUpdateKeteranganDet = [];
            for (var i = 0; i < dataUnit.length; i++) {
                var temp = {
                    COMPANY: stockTakingData["company"],
                    STOCK_TAKING_UNIT_ID: stockTakingData["stockTakingUnitId"],
                    MESIN: dataUnit[i]["MESIN"],
                    KETERANGAN: `${dataUnit[i]["TIMESTAMP"]}${(userId!= null || userId=='')?' by '+ userId:''}`,
                };
                paramsUpdateKeteranganDet.push(temp);
            }

            await oracleFunc.runQueryMany(
                conn,
                queryUpdateKeteranganDet,
                paramsUpdateKeteranganDet
            );
        }

        if (dataFoto.length > 0) {
            var queryStockUnitPic = `
                BEGIN
                    ${owner}.STOCK_TAKING_UNIT_UTILS_API.Create_Stock_Taking_Blob( 
                       company_                => :COMPANY, 
                       stock_taking_unit_id_   => :STOCK_TAKING_ID, 
                       mesin_                  => :MESIN, 
                       no_urut_                => :NO_URUT, 
                       jenis_                  => :JENIS, 
                       latitude_               => :LATITUDE, 
                       longitude_              => :LONGITUDE, 
                       blob_file_              => :BLOB_FILE   
                      ); 
                END;
            `;
            var paramsUnitDetPic = [];
            for (var j = 0; j < dataFoto.length; j++) {
                var temp = {
                    COMPANY: stockTakingData["company"],
                    STOCK_TAKING_ID: stockTakingData["stockTakingUnitId"],
                    MESIN: dataFoto[j]["MESIN"],
                    NO_URUT: dataFoto[j]["NO_URUT"],
                    JENIS: dataFoto[j]["JENIS"],
                    LATITUDE: dataFoto[j]["LATITUDE"],
                    LONGITUDE: dataFoto[j]["LONGITUDE"],
                    BLOB_FILE: dataFoto[j]["BLOB_FILE"],
                };
                paramsUnitDetPic.push(temp);
            }

            await oracleFunc.runQueryMany(
                conn,
                queryStockUnitPic,
                paramsUnitDetPic
            );
        }

        await oracleFunc.doCommit(conn);
        await oracleFunc.doRelease(conn);

        return "Data has been processed";
    } catch (e) {
        oracleFunc.doRollBack(conn);
        throw e;
    }
}

module.exports = {
    getDataCabang: getDataCabang,
    getDataUnit: getDataUnit,
    getDataUser: getDataUser,
    prepareStockCollection: prepareStockCollection,
    insertStockUnitData: insertStockUnitData,
    getActiveStockTakingByCabang: getActiveStockTakingByCabang,
    getActiveStockCollection: getActiveStockCollection,
    getDataCabangByUser: getDataCabangByUser,
    submitStockTaking: submitStockTaking,
    getStateStockTaking: getStateStockTaking,
};