Subversion Repositories SmartDukaan

Rev

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

Rev 10808 Rev 11220
Line 67... Line 67...
67
67
68 
  <select id="getPurchaseScansByGrnDate" parameterType="map" resultType="detailedPurchaseScan">
 68 
  <select id="getPurchaseScansByGrnDate" parameterType="map" resultType="detailedPurchaseScan">
69 
    SELECT p.purchaseOrder_id AS purchaseOrderId, 1000 * UNIX_TIMESTAMP(po.createdAt) AS poCreatedAt, s.name AS supplierName,
 69 
    SELECT p.purchaseOrder_id AS purchaseOrderId, 1000 * UNIX_TIMESTAMP(po.createdAt) AS poCreatedAt, s.name AS supplierName,
70 
        p.invoiceNumber AS invoiceNumbers, inv.receivedFrom AS receivedBy, i.itemId,brand, modelName, modelNumber, color,
70 
        p.invoiceNumber AS invoiceNumbers, inv.receivedFrom AS receivedBy, i.itemId,brand, modelName, modelNumber, color,
71 
        unitPrice, SUM(initialQuantity) AS quantity, 1000 * UNIX_TIMESTAMP(receivedOn) as purchasedAt, p.id as purchaseId,
71 
        unitPrice, SUM(initialQuantity) AS quantity, 1000 * UNIX_TIMESTAMP(receivedOn) as purchasedAt, p.id as purchaseId,
72 
        nlc, po.warehouseId, po.taxType
 72 
        nlc, po.warehouseId, po.taxType, inv.invoiceDate as invoiceDate
73 
    FROM purchase p
 73 
    FROM purchase p
74 
    JOIN purchaseorder po
74 
    JOIN purchaseorder po
75 
        ON (po.id = p.purchaseOrder_id)
 75 
        ON (po.id = p.purchaseOrder_id)
76 
    JOIN inventoryItem i
 76 
    JOIN inventoryItem i
77 
        ON (p.id = i.purchaseId)
 77 
        ON (p.id = i.purchaseId)
Line 116... Line 116...
116 
        SUM((age &gt;= 3 and age &lt; 4) * currentQuantity) AS threeToFourCount,
116 
        SUM((age &gt;= 3 and age &lt; 4) * currentQuantity) AS threeToFourCount,
117 
        SUM((age &gt;= 4) * currentQuantity) as fourPlusCount,
 117 
        SUM((age &gt;= 4) * currentQuantity) as fourPlusCount,
118 
        SUM((age &gt;= 1) * currentQuantity) as onePlusCount,
 118 
        SUM((age &gt;= 1) * currentQuantity) as onePlusCount,
119 
        SUM((age &gt;= 13) * currentQuantity) as threeMonthPlusCount,
 119 
        SUM((age &gt;= 13) * currentQuantity) as threeMonthPlusCount,
120 
        SUM((age &gt;= 26) * currentQuantity) as sixMonthPlusCount,
 120 
        SUM((age &gt;= 26) * currentQuantity) as sixMonthPlusCount,
- 
 
 121 
        SUM((age &gt; 0) and (age &lt;= 13) * currentQuantity) AS zeroToThreeMonthCount,
- 
 
 122 
        SUM((age &gt; 13) and (age &lt;= 26) * currentQuantity) AS threeToSixMonthCount,
- 
 
 123 
        SUM((age &gt; 26) and (age &lt;= 52) * currentQuantity) AS sixToTwelveMonthCount,
- 
 
 124 
        SUM((age &gt; 52) * currentQuantity) AS twelveMonthsPlusCount,
121 
        SUM((age &gt;= 1) * unitPrice*currentQuantity) as onePlusCost,
 125 
        SUM((age &gt;= 1) * unitPrice*currentQuantity) as onePlusCost,
122 
        SUM(currentQuantity) as zeroPlusCount,
 126 
        SUM(currentQuantity) as zeroPlusCount,
123 
        SUM(l.unitPrice*currentQuantity) as zeroPlusCost,
 127 
        SUM(l.unitPrice*currentQuantity) as zeroPlusCost,
124 
        l.productGroup as category
 128 
        l.productGroup as category
125 
    FROM  (SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId,
 129 
    FROM  (SELECT ii.id, ii.itemId, ii.currentQuantity, ii.purchaseId,