OTC-780: update business logic (#160)

Jan · web-flow · commit 828dd24d0e38 · 2023-02-16T14:58:47.000+01:00
* OTC-780: update business logic: items/serives left is now being calculate as a sum of quantity in all valid claims assigned to insuree

* OTC-780: refactor the code

* Update openmis-module-test.yml
diff --git a/.github/workflows/openmis-module-test.yml b/.github/workflows/openmis-module-test.yml
@@ -11,7 +11,7 @@ on:
 
 jobs:
   run_test:
-    runs-on: ubuntu-latest
+    runs-on: ubuntu-20.04
     services:
       mssql:
         image: mcr.microsoft.com/mssql/server:2017-latest
diff --git a/sql/stored_procedures/uspServiceItemEnquiry.sql b/sql/stored_procedures/uspServiceItemEnquiry.sql
@@ -40,7 +40,7 @@ BEGIN
 		INSERT INTO @tblService
 		SELECT IP.EffectiveDate, PL.ProdID,
 		DATEADD(MONTH,CASE WHEN @Age >= 18 THEN  PS.WaitingPeriodAdult ELSE PS.WaitingPeriodChild END,IP.EffectiveDate) MinDate,
-		(CASE WHEN @Age >= 18 THEN NULLIF(PS.LimitNoAdult,0) ELSE NULLIF(PS.LimitNoChild,0) END) - COUNT(CS.ServiceID) ServicesLeft
+		(CASE WHEN @Age >= 18 THEN NULLIF(PS.LimitNoAdult,0) ELSE NULLIF(PS.LimitNoChild,0) END) - SUM(COALESCE(CS.QtyApproved, CS.QtyProvided)) ServicesLeft
 		FROM tblInsureePolicy IP INNER JOIN tblPolicy PL ON IP.PolicyId = PL.PolicyID
 		INNER JOIN tblProductServices PS ON PL.ProdID = PS.ProdID
 		LEFT OUTER JOIN tblClaim C ON IP.InsureeId = C.InsureeID
@@ -78,7 +78,7 @@ BEGIN
 		INSERT INTO @tblItem
 		SELECT IP.EffectiveDate, PL.ProdID,
 		DATEADD(MONTH,CASE WHEN @Age >= 18 THEN  PItem.WaitingPeriodAdult ELSE PItem.WaitingPeriodChild END,IP.EffectiveDate) MinDate,
-		(CASE WHEN @Age >= 18 THEN NULLIF(PItem.LimitNoAdult,0) ELSE NULLIF(PItem.LimitNoChild,0) END) - COUNT(CI.ItemID) ItemsLeft
+		(CASE WHEN @Age >= 18 THEN NULLIF(PItem.LimitNoAdult,0) ELSE NULLIF(PItem.LimitNoChild,0) END) - SUM(COALESCE(CI.QtyApproved, CI.QtyProvided)) ItemsLeft
 		FROM tblInsureePolicy IP INNER JOIN tblPolicy PL ON IP.PolicyId = PL.PolicyID
 		INNER JOIN tblProductItems PItem ON PL.ProdID = PItem.ProdID
 		LEFT OUTER JOIN tblClaim C ON IP.InsureeId = C.InsureeID
