Subversion Repositories SmartDukaan

  <select id="getPurchaseScans" parameterType="map" resultType="detailedPurchaseScan">

    SELECT p.purchaseOrder_id AS purchaseOrderId, po.createdAt AS poCreatedAt, s.name AS supplierName,

        GROUP_CONCAT(DISTINCT(invoiceNumber)) AS invoiceNumbers, i.itemId,

        CONCAT_WS(" ", brand, modelName, modelNumber, color) AS itemName, unitPrice, COUNT(*) AS quantity

    FROM purchase p

    JOIN purchaseorder po 

        ON (po.id = p.purchaseOrder_id)

    JOIN inventoryItem i

        ON (p.id = i.purchaseId)

    JOIN lineitem l

        ON (l.itemId = i.itemId AND l.purchaseOrder_id = po.id)

    JOIN supplier s

        ON (s.id = po.supplierId)

    WHERE po.createdAt BETWEEN #{startDate} AND #{endDate} 

    GROUP BY p.purchaseOrder_id, i.itemId

    ORDER BY p.purchaseOrder_id, i.itemId

  </select>