Subversion Repositories SmartDukaan

Rev

Rev 5545 | Rev 5768 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 5545 Rev 5711
Line 60... Line 60...
60 
    WHERE i.date = #{date}
 60 
    WHERE i.date = #{date}
61 
    GROUP BY i.invoiceNumber, i.numItems
 61 
    GROUP BY i.invoiceNumber, i.numItems
62 
    HAVING SUM(initialQuantity) != i.numItems
 62 
    HAVING SUM(initialQuantity) != i.numItems
63 
  </select>
 63 
  </select>
64 
 
 64 
 
- 
 
 65 
  <select id="getInventoryAge" resultType="inventoryAge">
- 
 
 66 
    SELECT t.itemId, brand, modelName, modelNumber, color,
- 
 
 67 
        SUM((age &lt; 1) * currentQuantity) AS freshCount,
- 
 
 68 
        SUM((age between 1 and 2) * currentQuantity) AS oneToTwoCount,
- 
 
 69 
        SUM((age between 2 and 3) * currentQuantity) AS twoToThreeCount,
- 
 
 70 
        SUM((age between 3 and 4) * currentQuantity) AS threeToFourCount,
- 
 
 71 
        SUM((age &gt; 4) * currentQuantity) as fourPlusCount
- 
 
 72 
    FROM  (SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId,
- 
 
 73 
                DATEDIFF(now(), MAX(s.scannedAt)) / 7 as age
- 
 
 74 
           FROM inventoryItem ii
- 
 
 75 
           JOIN scanNew s
- 
 
 76 
                ON (s.inventoryItemId = ii.id)
- 
 
 77 
           WHERE ii.lastScanType IN ('PURCHASE', 'SALE_RET')
- 
 
 78 
           GROUP BY ii.id, ii.itemId, ii.currentQuantity) t
- 
 
 79 
    JOIN purchase p
- 
 
 80 
        ON (p.id = t.purchaseId)
- 
 
 81 
    JOIN lineitem l
- 
 
 82 
        ON (l.purchaseOrder_id = p.purchaseOrder_id AND l.itemId = t.itemId)
- 
 
 83 
    GROUP BY t.itemId
- 
 
 84 
    HAVING SUM(currentQuantity) != 0
- 
 
 85 
  </select>
65 
</mapper>
 86 
</mapper>