first upload

[Pman.Xtuple] / sqlreports / perbrand.sql

diff --git a/sqlreports/perbrand.sql b/sqlreports/perbrand.sql
new file mode 100644 (file)
index 0000000..9c6b7fc
--- /dev/null
+++ b/sqlreports/perbrand.sql
@@ -0,0 +1,73 @@
+
+-- this is proffit
+SELECT
+         
+        itemcharvalue(invcitem_item_id, 'BRAND')  AS rkey,
+        itemcharvalue(invcitem_item_id, 'BRAND') AS rkid,
+         
+        ROUND(sum(invcitem_billed),0) as qtysold,
+        ROUND(sum(
+        
+                invcitem_billed *
+                    currtocurr(
+                        invchead_curr_id,
+                        getcurrid('HKD'),
+                        invcitem_custprice ,
+                        invchead_invcdate
+                    )
+        ),0) as rvalue 
+         
+        FROM
+         
+            invcitem
+      
+         
+        LEFT JOIN
+            invchead
+        ON
+            invchead_id = invcitem_invchead_id
+        
+        
+        
+        LEFT JOIN
+            custinfo
+        ON
+            invchead_cust_id = cust_id
+        
+        LEFT JOIN
+            cntct
+        ON
+            cntct_id = cust_cntct_id 
+        
+        LEFT JOIN
+            addr
+        ON
+            addr_id = cntct_addr_id 
+        
+        
+        
+        WHERE
+            
+            invchead_invcdate >= '$dateFrom'
+            AND
+            invchead_invcdate  <= '$dateUpto'
+            AND
+            invchead_void = false
+            AND
+            
+            '$country' = CASE
+                WHEN '$country' = '' THEN
+                    '$country'
+                WHEN (addr_country IS NULL OR addr_country = '') THEN
+                    'UNKNOWN'
+                ELSE 
+                    addr_country    
+                END 
+            
+        GROUP BY
+            rkey, rkid 
+            
+          ORDER BY
+             rvalue DESC
+        LIMIT 10
+    
\ No newline at end of file