get_shift_details_based_on_location

get_shift_details_based_on_location.sql

CREATE OR REPLACE FUNCTION public.get_shift_details_based_on_location (invid INTEGER)
    RETURNS TABLE (product_sku_id INTEGER, product_name CHAR VARYING, g_code CHAR VARYING, s_code CHAR VARYING, m_code CHAR VARYING [ ], location_id INTEGER, location_name CHAR VARYING, time_finalization TIMESTAMP WITHOUT TIME ZONE, confirmed_quantity INTEGER, finalized_quantity INTEGER, current_quantity INTEGER, inventoried_list TEXT [ ], confirmed_list TEXT [ ])
AS $function$
BEGIN
    RETURN QUERY
    SELECT
        sif.product_sku_id product_sku_id,
        pt.name product_name,
        ps.g_code g_code,
        ps.s_code s_code,
        ARRAY (
            SELECT
                tag.name
            FROM
                product_mcode_tag tag
                INNER JOIN product_supplierinfo_m_code_rel rel ON rel.m_code_id = tag.id
                    AND rel.product_supplierinfo_id = ps.id) m_code,
        icc.location_id location_id,
        sl.name location_name,
        sif.time_finalization time_finalization,
        sifl.count_confirmation::INTEGER confirmed_quantity,
        sif.count_finalization::INTEGER finalized_quantity,
        sif.count_inventory::INTEGER current_quantity,
        inv_prod_compt.inventoried_list inventoried_list,
        inv_final_log.confirmed_list confirmed_list
    FROM (
        SELECT
            t10.compartment_id location_id
        FROM
            inventorying_check_compartment t10
    WHERE
        t10.inventorying_id = invid) icc
        LEFT JOIN (
            SELECT
                t0.product_sku_id, t0.location_id, t0.count_inventory count_inventory, t0.count_finalization count_finalization, t0.time_finalization
            FROM
                stock_inventorying_finalization t0
            WHERE
                t0.stock_inventorying_id = invid) sif ON sif.location_id = icc.location_id
        LEFT JOIN (
            SELECT
                t2.product_sku_id, t2.location_id, array_agg('{ "time_inventoried": ' || t2.time_inventoried || ', "quantity": ' || t2.count_practicality || ', "time_check_end": "' || t2.date_end || '", "checker_name": "' || t4.name || '"}') inventoried_list
            FROM
                stock_inventorying_line t2
                LEFT JOIN res_users t3 ON t3.id = t2.checker_id
                LEFT JOIN res_partner t4 ON t4.id = t3.partner_id
            WHERE
                t2.stock_inventorying_id = invid
            GROUP BY
                t2.product_sku_id,
                t2.location_id) inv_prod_compt ON inv_prod_compt.product_sku_id = sif.product_sku_id
        AND inv_prod_compt.location_id = sif.location_id
        LEFT JOIN (
            SELECT
                sifl.product_sku_id,
                sifl.location_id,
                array_agg('{ "decision_maker_name": "' || t7.name || '", "time_confirmation": "' || sifl.time_confirmation || '", "count_confirmation": ' || sifl.count_confirmation || '}') confirmed_list
            FROM
                stock_inventorying_finalization_log sifl
                LEFT JOIN res_users t6 ON t6.id = sifl.decision_maker_id
                LEFT JOIN res_partner t7 ON t7.id = t6.partner_id
            WHERE
                sifl.stock_inventorying_id = invid
            GROUP BY
                sifl.product_sku_id,
                sifl.location_id) inv_final_log ON inv_final_log.product_sku_id = sif.product_sku_id
        AND inv_final_log.location_id = sif.location_id
        LEFT JOIN (
            SELECT
                t8.product_sku_id,
                t8.location_id,
                SUM(t8.count_confirmation) count_confirmation
            FROM
                stock_inventorying_finalization_log t8
            WHERE
                t8.stock_inventorying_id = invid
                AND (t8.product_sku_id, t8.location_id, t8.time_confirmation)
                IN (
                    SELECT
                        t9.product_sku_id, t9.location_id, MAX(time_confirmation) time_confirmation
                    FROM
                        stock_inventorying_finalization_log t9
                    WHERE
                        t9.stock_inventorying_id = invid
                    GROUP BY
                        t9.product_sku_id,
                        t9.location_id)
                GROUP BY
                    t8.product_sku_id,
                    t8.location_id) sifl ON sifl.product_sku_id = sif.product_sku_id
        AND sifl.location_id = sif.location_id
        LEFT JOIN stock_location sl ON sl.id = icc.location_id
        LEFT JOIN product_supplierinfo ps ON ps.id = sif.product_sku_id
        LEFT JOIN product_template pt ON pt.id = ps.product_tmpl_id;
END;
$function$
LANGUAGE plpgsql;