Changes between Version 10 and Version 11 of AdvancedReports

Timestamp:

01/01/23 15:24:36 (3 years ago)

Author:

201065

Comment:

--

AdvancedReports

@@ -65 +65 @@
 }}}
 
+===  Предвидување дали одреден артикл во одреден магацин треба да се нарача во однос на неговота достапност и потрошувачка во последниот месец дена ===
+
+{{{#!sql
+select a.articlename, l.locationname,
+        (case
+                when q1.dostapnost+coalesce(q2.incoming, 0)<coalesce(q3.sold, 0) then 'Yes'
+                else 'No'
+        end
+        ) as defict from articles a
+left join storedarticles s on s.articleid=a.articleid
+left join locations l on l.locationid=s.locationid
+left join (
+        select articleid, locationid, coalesce(sum(quantity), 0) as dostapnost from storedarticles s
+        group by s.sarticleid, s.locationid
+) q1 on q1.articleid=a.articleid and q1.locationid=l.locationid
+left join (
+        select articleid, locationid, coalesce(sum(quantity), 0) as incoming from orderedarticles o
+        where o.articlestatus='In progress' or o.articlestatus='Delivered'
+        group by o.articleid, o.locationid
+) q2 on q2.articleid=a.articleid and q2.locationid=l.locationid
+left join (
+        select i.articleid, l.locationid, coalesce(sum(quantity), 0) as sold from invoicedarticles i
+        left join invoices inv on inv.invoiceid=i.invoiceid and inv.datecreate between now()-interval '1 month' and now()
+        left join workers w on w.userid=inv.workeruserid
+        left join locations l on l.locationid=w.locationid
+        group by i.articleid, l.locationid
+)q3 on q3.articleid=a.articleid and q3.locationid=l.locationid
+order by a.articlename 
+
+}}}
+
 ===  Извештај за профитот на сите магацини според набавени артикли и продадени артикли во период од една година===