Backend-POS/Controller/Inventory_Movement.js

const asyncHandler = require("../Middleware/async");
const sequelize = require('sequelize')
const db = require("../Config/db");
const ErrorResponse = require("../utils/errorResponse");
const { getInventoryMovementTable } = require("../Model/Inventory_Movement_Model");
const ItemModel = require("../Model/ItemModel");


exports.getAllStock = asyncHandler(async (req, res, next) => {
    try {
        const userDBCode = req.user.DB_CODE;
        if (!userDBCode) {
            return next(new ErrorResponse("Store Code not found or disabled", 400));
        }

        const InventoryModel = getInventoryMovementTable(`${userDBCode}TDINVMOV`);
        let allInventory = await InventoryModel.findAll({
            raw: true
        });

        // Map over all inventory items and fetch associated item details
        const inventoryWithDetails = await Promise.all(allInventory.map(async (inv) => {
            const item = await ItemModel.findOne({
                where: {
                    ITEM_CODE: inv.ITEM_CODE,
                    DB_CODE: userDBCode
                },
                attributes: ['ITEM_BCODE', 'ITEM_DESC', 'ITEM_COST1', 'ITEM_PRICE1', 'CAT_CODE'],  // specify only the necessary fields to fetch
                raw: true
            });

            // Merge item details with inventory data
            return {
                ...inv,
                ITEM_NAME: item ? item.ITEM_DESC : null,
                ITEM_IMAGE: item ? item.ITEM_IMG : null,
                ITEM_COST1: item ? item.ITEM_COST1 : null,
                ITEM_PRICE1: item ? item.ITEM_PRICE1 : null,
            };
        }));

        return res.status(200).json({
            success: true,
            data: inventoryWithDetails
        });
    } catch (err) {
        console.log("ERROR GET ALL STORE", err);
        return next(new ErrorResponse("Failed to retrieve stock", 400));  // More specific error message
    }
});


exports.createStock = asyncHandler(async (req, res, next) => {
    const t = await db.transaction()
    try {
        const userDBCode = req.user.DB_CODE
        if (!userDBCode) {
            return next(new ErrorResponse("Store Code not found or disabled", 400));
        }

        const InventoryModel = getInventoryMovementTable(`${userDBCode}TDINVMOV`)
        const maxSequenceQty = await db.query(`SELECT MAX(SEQUENCE)+1 AS MAX FROM ${userDBCode}TDINVMOV`,
            {
                transaction: t,
                type: sequelize.QueryTypes.SELECT
            }).catch((err) => {
                throw `Cannot query max sequence ${err}`;
            })

        // check whether ITEM_CODE exists in the database
        const itemCode = await ItemModel.findOne({
            where: {
                ITEM_CODE: req.body.ITEM_CODE,
                DB_CODE: userDBCode
            }
        })

        if (!itemCode) {
            return next(new ErrorResponse("Item Code not found", 400));
        }


        const { MOV_PRD, MOV_REF, MOV_LINE, LOCATION, ITEM_CODE, MOV_DATE, STATUS, IR_STAT, BATCH_NO, BATCH_LINE, LINE_REF, QUANTITY, COST, TOTAL } = req.body
        const newInventory = await InventoryModel.create({
            SEQUENCE: maxSequenceQty[0]?.MAX || 1,
            ID_ALLOC: req.user.USER_ID,
            REC_TYPE: "T",
            MOV_UNITS: "1",
            MOV_TYPE: "OB",
            UPDTE_PHYS: "",
            UPDTE_ORDR: "",
            ALLOC_REF: req.user.DB_CODE + generateTimestampBasedAllocRef(),
            ACCNT_CODE: "",
            ASSET_CODE: req.body.ASSET_CODE || "",
            ANAL_M0: req.body.ANAL_M0 || "",
            ANAL_M1: req.body.ANAL_M1 || "",
            ANAL_M2: req.body.ANAL_M2 || "",
            ANAL_M3: req.body.ANAL_M3 || "",
            ANAL_M4: req.body.ANAL_M4 || "",
            ANAL_M5: req.body.ANAL_M5 || "",
            ANAL_M6: req.body.ANAL_M6 || "",
            ANAL_M7: req.body.ANAL_M7 || "",
            ANAL_M8: req.body.ANAL_M8 || "",
            ANAL_M9: req.body.ANAL_M9 || "",
            ORIG_LINE_NO: req.body.ORIG_LINE_NO || "",
            PO_VALUE: req.body.PO_VALUE || 0,
            ID_ENTERED: req.user.USER_NAME,
            ID_ALLOC: req.user.USER_NAME,

            MOV_PRD, MOV_REF, MOV_LINE, LOCATION, ITEM_CODE, MOV_DATE, STATUS, IR_STAT, BATCH_NO, BATCH_LINE, LINE_REF, QUANTITY, COST, TOTAL,

        })
        // const allInventory = await InventoryModel.findAll()
        return res.status(200).json({
            success: true,
            data: newInventory
        })
    } catch (err) {
        console.log("ERROR GET ALL STORE", err)
        await t.rollback()
        return next(new ErrorResponse(err, 400));
    }
})

function generateTimestampBasedAllocRef() {
    const now = new Date();
    const year = now.getFullYear();
    const month = (now.getMonth() + 1).toString().padStart(2, '0'); // Month is zero-indexed, add 1 and pad it to ensure two digits
    const day = now.getDate().toString().padStart(2, '0');
    const hours = now.getHours().toString().padStart(2, '0');
    const minutes = now.getMinutes().toString().padStart(2, '0');
    const seconds = now.getSeconds().toString().padStart(2, '0');
    const milliseconds = now.getMilliseconds().toString().padStart(3, '0');

    const allocRef = `-${year}${month}${day}${hours}${minutes}`;
    console.log("ALLOC REF", allocRef)
    return allocRef;
}