Changeset 547

Timestamp:

May 26, 2010, 3:50:50 AM (14 years ago)

Author:

gav

Message:

Improvements to Inventory Stock Take reports, MSSQL does not like using distinct the way it was, also improved protection against too many results.

Location:

trunk

Files:

• grails-app/i18n/messages.properties (modified) (1 diff)
• grails-app/services/InventoryReportService.groovy (modified) (4 diffs)
• grails-app/views/inventoryItemPurchaseDetailed/edit.gsp (modified) (2 diffs)
• web-app/reports/stockTakeByLocation.jrxml (modified) (2 diffs)

trunk/grails-app/i18n/messages.properties

@@ -315 +315 @@
 
 # Reports
+report.too.many.results.warning=Warning over {0} results, please run report again.
 report.stock.take.overview=Stock Take (Overview)
 report.stock.take.overview.help=Use this report to manage inventory stock take. Use in conjunction with the Stock Take (By Location) report.

trunk/grails-app/services/InventoryReportService.groovy

@@ -13 +13 @@
     def g = new org.codehaus.groovy.grails.plugins.web.taglib.ApplicationTagLib()
 
-//     def paramsMax = 100000
+    def paramsMax = 250
 
     /**
@@ -61 +61 @@
         result.locations = result.locations.collect {it.trim()}
 
+        def paginateParams = [:]
+        paginateParams.max = Math.min(params?.max?.toInteger() ?: paramsMax, paramsMax)
+
         def namedParams = [:]
         namedParams.locationList = [null] // null protects against HQL unexpected end of subtree exception with an empty list.
@@ -71 +74 @@
         }
 
+        // Return the actual locations as a string.
+        if(namedParams.locationList.size() > 1)
+            result.locations = namedParams.locationList[1..-1].toString()[1..-2]
+        else
+            result.locations = g.message(code: 'default.none.text')
+
+        // Inventory List.
+        result.inventoryListQuery = "from InventoryItem as inventoryItem \
+                                                        left join inventoryItem.inventoryLocation as inventoryLocation \
+                                                        where (inventoryItem.isActive = true \
+                                                                    and  inventoryItem.inventoryLocation in (:locationList) \
+                                                                    ) "
+
+        result.inventoryCountQuery = "select count(distinct inventoryItem) " + result.inventoryListQuery
+        result.inventoryItemCount = InventoryItem.executeQuery(result.inventoryCountQuery, namedParams)[0]
+
+        // Exit if too many results.
+        result.countWarning = null
+        if(result.inventoryItemCount > paramsMax) {
+            result.countWarning = g.message(code: 'report.too.many.results.warning',
+                                                                    args: [paramsMax],
+                                                                    default: "Warning over ${paramsMax} results, please run report again!")
+            result.inventoryItemList = []
+            return result
+        }
+
+        result.inventoryListQuery = "select distinct inventoryItem " + result.inventoryListQuery
+        def inventoryList = InventoryItem.executeQuery(result.inventoryListQuery, namedParams, paginateParams)
+
+        // Reset namedParams for next query.
+        namedParams = [:]
+        namedParams.inventoryList = inventoryList
+
         // Note: HQL docs advise not using fetch aliases in where clause (or any other clause).
         // Access is via the parent object, however that does not work for the order by clause in this case.
@@ -79 +115 @@
                                         left join fetch inventoryItem.picture as picture \
                                         left join fetch picture.images as Image \
-                                        where (inventoryItem.isActive = true \
-                                                    and  inventoryItem.inventoryLocation in (:locationList) \
+                                        where (inventoryItem in (:inventoryList) \
                                                     ) \
                                         order by inventoryStore.name, inventoryLocation.name"
 
-        result.query = "select distinct inventoryItem " + result.query
-        result.queryResult = InventoryLocation.executeQuery(result.query, namedParams)
-        result.inventoryItemCount = result.queryResult.size()
-
-        // Return the actual locations as a string.
-        if(namedParams.locationList.size() > 1)
-            result.locations = namedParams.locationList[1..-1].toString()[1..-2]
-        else
-            result.locations = g.message(code: 'default.none.text')
-
-        result.inventoryItemList = result.queryResult
+        result.query = "select inventoryItem " + result.query
+        result.inventoryItemList = InventoryItem.executeQuery(result.query, namedParams, paginateParams)
 
         // Success.

trunk/grails-app/views/inventoryItemPurchaseDetailed/edit.gsp

@@ -40 +40 @@
                         
                             <tr class="prop">
-                                <td valign="top" class="name">Purchase Order Number:</td>
-                                
-                                <td valign="top" class="value">${fieldValue(bean:inventoryItemPurchaseInstance, field:'purchaseOrderNumber')}</td>
-                                
+                                <td valign="top" class="name">
+                                    <label for="invoiceNumber">Purchase Order Number:</label>
+                                </td>
+                                <td valign="top" class="value ${hasErrors(bean:inventoryItemPurchaseInstance,field:'purchaseOrderNumber','errors')}">
+                                    <input type="text" maxlength="50" id="purchaseOrderNumber" name="purchaseOrderNumber" value="${fieldValue(bean:inventoryItemPurchaseInstance,field:'purchaseOrderNumber')}"/>
+                                </td>
                             </tr>
                         
@@ -139 +141 @@
                             </tr>
                         
+                            <tr class="prop">
+                                <td valign="top" class="name">
+                                    <label for="comment">Comment:</label>
+                                </td>
+                                <td valign="top" class="value ${hasErrors(bean:inventoryItemPurchaseInstance,field:'comment','errors')}">
+                                    <textarea rows="5" cols="40" name="comment">${fieldValue(bean:inventoryItemPurchaseInstance, field:'comment')}</textarea>
+                                </td>
+                            </tr>
+                        
                         </tbody>
                     </table>

trunk/web-app/reports/stockTakeByLocation.jrxml

@@ -85 +85 @@
         <field name="inventoryItemCount" class="java.lang.Integer"/>
         <field name="locations" class="java.lang.String"/>
+        <field name="countWarning" class="java.lang.String"/>
         <background>
                 <band splitType="Stretch"/>
@@ -105 +106 @@
                                 <textElement textAlignment="Center" verticalAlignment="Middle" markup="none"/>
                                 <textFieldExpression class="java.lang.String"><![CDATA["Inventory Items: "+$F{inventoryItemCount}+", Locations: "+$F{locations}+"."]]></textFieldExpression>
+                        </textField>
+                        <textField>
+                                <reportElement mode="Opaque" x="400" y="4" width="382" height="46" isPrintWhenDetailOverflows="true" forecolor="#FF0000" backcolor="#FFCCCC">
+                                        <printWhenExpression><![CDATA[$F{countWarning} != null ? true:false]]></printWhenExpression>
+                                </reportElement>
+                                <textElement textAlignment="Center" verticalAlignment="Middle">
+                                        <font size="12" isBold="true"/>
+                                </textElement>
+                                <textFieldExpression class="java.lang.String"><![CDATA[$F{countWarning}]]></textFieldExpression>
                         </textField>
                 </band>