Subversion Repositories SmartDukaan

Rev

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

Rev 7216 Rev 7224
Line 76... Line 76...
76 
    JOIN lineitem l
 76 
    JOIN lineitem l
77 
        ON (l.itemId = i.itemId AND l.purchaseOrder_id = po.id)
 77 
        ON (l.itemId = i.itemId AND l.purchaseOrder_id = po.id)
78 
    JOIN supplier s
 78 
    JOIN supplier s
79 
        ON (s.id = po.supplierId)
 79 
        ON (s.id = po.supplierId)
80 
    JOIN invoice inv
 80 
    JOIN invoice inv
81 
    	ON (inv.invoiceNumber = p.invoiceNumber)
 81 
    	ON (inv.invoiceNumber = p.invoiceNumber AND inv.supplierId = po.supplierId)
82 
    WHERE p.receivedOn BETWEEN #{startDate} AND #{endDate}
 82 
    WHERE p.receivedOn BETWEEN #{startDate} AND #{endDate}
83 
    AND inv.date  < #{endDate}
 83 
    AND inv.date  < #{endDate}
- 
 
 84 
    AND inv.date  > ADDDATE(#{startDate},-1)
84 
    AND lastScanType != 'DOA_REPLACED'
85 
    AND lastScanType != 'DOA_REPLACED'
85 
    GROUP BY p.id, i.itemId
 86 
    GROUP BY p.purchaseOrder_id, p.id, i.itemId
86 
    ORDER BY p.id, i.itemId
 87 
    ORDER BY p.purchaseOrder_id, p.id, i.itemId
87 
  </select>
 88 
  </select>
88
89
89 
  <select id="fetchMismatchScansPerInvoiceNumber" parameterType="java.util.Date" resultType="invoiceScan" >
 90 
  <select id="fetchMismatchScansPerInvoiceNumber" parameterType="java.util.Date" resultType="invoiceScan" >
90 
    SELECT i.invoiceNumber, i.numItems, sum(initialQuantity) as scannedQuantity, i.date, s.name as supplierName
 91 
    SELECT i.invoiceNumber, i.numItems, sum(initialQuantity) as scannedQuantity, i.date, s.name as supplierName
91 
    FROM invoice i
 92 
    FROM invoice i