product_quantities.pb.js

/// <reference path="../pb_data/types.d.ts" />
routerAdd(
  "GET",
  "/api/custom/product-quantities",
  (c) => {
    const page = parseInt(c.queryParam("page") || "1", 10);
    const perPage = parseInt(c.queryParam("perPage") || "20", 10);
    const search = c.queryParam("search") || "";

    try {
      const productQuantityModel = new DynamicModel({
        product_id: "",
        product_name: "",
        material_type: "",
        format: "",
        size: "",
        quality_grade: "",
        unit_of_measurement: "",
        total_ordered: 0,
        total_shipped: 0,
        total_excess: 0,
        remaining_quantity: 0,
      });

      const result = arrayOf(productQuantityModel);

      $app
        .dao()
        .db()
        .newQuery(
          `
          WITH order_quantities AS (
              SELECT 
                  poi.product,
                  SUM(poi.qty) as total_ordered
              FROM 
                  PurchaseOrderItems poi
              GROUP BY 
                  poi.product
          ),
          shipped_quantities AS (
              SELECT 
                  poi.product,
                  SUM(si.quantityShipped) as total_shipped,
                  SUM(CASE WHEN si.quantityShipped > poi.qty THEN si.quantityShipped - poi.qty ELSE 0 END) as total_excess
              FROM 
                  ShipmentItems si
              JOIN
                  PurchaseOrderItems poi ON si.purchaseOrder = poi.purchaseOrder
              GROUP BY 
                  poi.product
          )
          SELECT 
              p.id as product_id,
              p.name as product_name,
              p.materialType as material_type,
              p.format as format,
              p.size as size,
              p.qualityGrade as quality_grade,
              p.unitOfMeasurement as unit_of_measurement,
              COALESCE(oq.total_ordered, 0) as total_ordered,
              COALESCE(sq.total_shipped, 0) as total_shipped,
              COALESCE(sq.total_excess, 0) as total_excess,
              CASE
                  WHEN COALESCE(oq.total_ordered, 0) > COALESCE(sq.total_shipped, 0) THEN COALESCE(oq.total_ordered, 0) - COALESCE(sq.total_shipped, 0)
                  ELSE 0
              END as remaining_quantity
          FROM 
              Products p
          LEFT JOIN 
              order_quantities oq ON p.id = oq.product
          LEFT JOIN 
              shipped_quantities sq ON p.id = sq.product
          WHERE 
              p.name LIKE '%' || {:search} || '%'
          ORDER BY 
              p.name
          LIMIT {:perPage} OFFSET {:offset}
          `,
        )
        .bind({
          search: search,
          perPage: perPage,
          offset: (page - 1) * perPage,
        })
        .all(result);

      // Count total items
      const totalItemsModel = new DynamicModel({
        total: 0,
      });

      $app
        .dao()
        .db()
        .newQuery(
          `
          SELECT COUNT(*) as total
          FROM Products p
          WHERE p.name LIKE '%' || {:search} || '%'
          `,
        )
        .bind({ search: search })
        .one(totalItemsModel);

      const totalItems = totalItemsModel.total;

      const productQuantities = result.map((row) => ({
        id: row.product_id,
        name: row.product_name,
        materialType: row.material_type,
        format: row.format,
        size: row.size,
        qualityGrade: row.quality_grade,
        unitOfMeasurement: row.unit_of_measurement,
        totalOrdered: row.total_ordered,
        totalShipped: row.total_shipped,
        totalExcess: row.total_excess,
        remainingQuantity: row.remaining_quantity,
      }));

      return c.json(200, {
        items: productQuantities,
        page: page,
        perPage: perPage,
        totalItems: totalItems,
        totalPages: Math.ceil(totalItems / perPage),
      });
    } catch (error) {
      console.error("Error fetching product quantities:", error);
      return c.json(500, { error: "Internal server error" });
    }
  },
  $apis.requireAdminOrRecordAuth(),
);