salix/back/methods/collection/getTickets.js

module.exports = Self => {
    Self.remoteMethodCtx('getTickets', {
        description: 'Make a new collection of tickets',
        accessType: 'WRITE',
        accepts: [{
            arg: 'id',
            type: 'number',
            description: 'The collection id',
            required: true,
            http: {source: 'path'}
        }, {
            arg: 'print',
            type: 'boolean',
            description: 'True if you want to print'
        }],
        returns: {
            type: ['object'],
            root: true
        },
        http: {
            path: `/:id/getTickets`,
            verb: 'POST'
        }
    });

    Self.getTickets = async(ctx, id, print, options) => {
        const userId = ctx.req.accessToken.userId;
        const url = await Self.app.models.Url.getUrl();
        const $t = ctx.req.__;
        const myOptions = {};

        if (typeof options == 'object')
            Object.assign(myOptions, options);

        myOptions.userId = userId;

        const promises = [];
        const [tickets] = await Self.rawSql(`CALL vn.collection_getTickets(?)`, [id], myOptions);

        const sales = await Self.rawSql(`
            SELECT s.ticketFk,
                    sgd.saleGroupFk,
                    s.id saleFk,
                    s.itemFk,
                    i.longName,
                    i.size,
                    ic.color,
                    o.code origin,
                    ish.packing,
                    ish.grouping,
                    s.isAdded,
                    s.originalQuantity,
                    s.quantity saleQuantity,
                    iss.quantity reservedQuantity,
                    SUM(iss.quantity) OVER (PARTITION BY s.id ORDER BY ish.id) accumulatedQuantity,
                    ROW_NUMBER () OVER (PARTITION BY s.id ORDER BY pickingOrder) currentItemShelving,
                    COUNT(*) OVER (PARTITION BY s.id ORDER BY s.id) totalItemShelving,
                    sh.code,
                    p2.code parkingCode,
                    p2.pickingOrder pickingOrder,
                    p.code parkingCodePrevia,
                    p.pickingOrder pickingOrderPrevia,
                    iss.id itemShelvingSaleFk,
                    iss.isPicked,
                    iss.itemShelvingFk
                FROM ticketCollection tc
                    LEFT JOIN collection c ON c.id = tc.collectionFk
                    JOIN sale s ON s.ticketFk = tc.ticketFk
                    LEFT JOIN saleGroupDetail sgd ON sgd.saleFk = s.id
                    LEFT JOIN saleGroup sg ON sg.id = sgd.saleGroupFk
                    LEFT JOIN parking p2 ON p2.id = sg.parkingFk
                    JOIN item i ON i.id = s.itemFk
                    JOIN itemShelvingSale iss ON iss.saleFk = s.id
                    LEFT JOIN itemShelving ish ON ish.id = iss.itemShelvingFk
                    LEFT JOIN shelving sh ON sh.id = ish.shelvingFk
                    LEFT JOIN parking p ON p.id = sh.parkingFk
                    LEFT JOIN itemColor ic ON ic.itemFk = s.itemFk
                    LEFT JOIN origin o ON o.id = i.originFk
                WHERE tc.collectionFk = ?
                GROUP BY s.id, ish.id, p.code, p2.code
            UNION ALL
            SELECT s.ticketFk,
                sgd.saleGroupFk,
                s.id saleFk,
                s.itemFk,
                i.longName,
                i.size,
                ic.color,
                o.code origin,
                ish.packing,
                ish.grouping,
                s.isAdded,
                s.originalQuantity,
                s.quantity,
                iss.quantity,
                SUM(iss.quantity) OVER (PARTITION BY s.id ORDER BY ish.id),
                ROW_NUMBER () OVER (PARTITION BY s.id ORDER BY p.pickingOrder),
                COUNT(*) OVER (PARTITION BY s.id ORDER BY s.id) ,
                sh.code,
                p2.code,
                p2.pickingOrder,
                p.code,
                p.pickingOrder,
                iss.id itemShelvingSaleFk,
                iss.isPicked,
                iss.itemShelvingFk
            FROM sectorCollection sc
                JOIN sectorCollectionSaleGroup ss ON ss.sectorCollectionFk  = sc.id 
                JOIN saleGroup sg ON sg.id = ss.saleGroupFk
                LEFT JOIN saleGroupDetail sgd ON sgd.saleGroupFk = sg.id
                JOIN sale s ON s.id = sgd.saleFk 
                LEFT JOIN parking p2 ON p2.id = sg.parkingFk
                JOIN item i ON i.id = s.itemFk
                JOIN itemShelvingSale iss ON iss.saleFk = s.id
                LEFT JOIN itemShelving ish ON ish.id = iss.itemShelvingFk
                LEFT JOIN shelving sh ON sh.id = ish.shelvingFk
                LEFT JOIN parking p ON p.id = sh.parkingFk
                LEFT JOIN itemColor ic ON ic.itemFk = s.itemFk
                LEFT JOIN origin o ON o.id = i.originFk
            WHERE sc.id = ? 
                AND sgd.saleGroupFk
            GROUP BY s.id, ish.id, p.code, p2.code`, [id, id], myOptions);
        if (print)
            await Self.rawSql(`CALL vn.collection_printSticker(?, ?)`, [id, null], myOptions);

        const collection = {collectionFk: id, tickets: []};

        if (tickets && tickets.length) {
            for (const ticket of tickets) {
                const ticketId = ticket.ticketFk;
                if (ticket.observation) {
                    for (observation of ticket.observation?.split(' ')) {
                        if (['#', '@'].includes(observation.charAt(0))) {
                            promises.push(Self.app.models.Chat.send(ctx, observation,
                                $t('The ticket is in preparation', {
                                    ticketId: ticketId,
                                    ticketUrl: `${url}ticket/${ticketId}/summary`,
                                    salesPersonId: ticket.salesPersonFk
                                })));
                        }
                    }
                }
                if (sales && sales.length) {
                    const barcodes = await Self.rawSql(`
                        SELECT s.id saleFk, b.code, c.id
                            FROM sale s
                                LEFT JOIN itemBarcode b ON b.itemFk = s.itemFk
                                LEFT JOIN buy c ON c.itemFk = s.itemFk
                                LEFT JOIN entry e ON e.id = c.entryFk
                                LEFT JOIN travel tr ON tr.id = e.travelFk
                            WHERE s.ticketFk = ?
                                AND tr.landed >= util.VN_CURDATE() - INTERVAL 1 YEAR`,
                    [ticketId], myOptions);
                    ticket.sales = [];
                    for (const sale of sales) {
                        if (sale.ticketFk === ticketId) {
                            sale.Barcodes = [];
                            if (barcodes && barcodes.length) {
                                for (const barcode of barcodes) {
                                    if (barcode.saleFk === sale.saleFk) {
                                        for (const prop in barcode) {
                                            if (['id', 'code'].includes(prop) && barcode[prop])
                                                sale.Barcodes.push(barcode[prop].toString(), '0' + barcode[prop]);
                                        }
                                    }
                                }
                            }
                            ticket.sales.push(sale);
                        }
                    }
                }
                collection.tickets.push(ticket);
            }
        }
        await Promise.all(promises);
        return collection;
    };
};