In synchronization we can execute the following code to put the availability of cashiers/positions combination’ s availability in a custom field .
Below we put in custom1 the availability of the funds 40.36 and in custom6 the cashiers/positions ids 1, 20.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
UPDATE "apouhkh" SET "modified"='NOW' , "custom1"= (select cast(round( ( sum(case "eidhpar"."Xrevstiko" when 0 then case when "eidhpar"."Emf_timvn" <> 2 then "grammes"."Posothta" else 0 end else case "eidhpar"."Xrevstiko" when 1 then case when "eidhpar"."Emf_timvn" = 2 then -"grammes"."Posothta" else 0 end else 0 end end ) - sum (case "eidhpar" . "Xrevstiko" when 0 then case when "eidhpar"."Emf_timvn" = 2 then -"grammes"."Posothta" else 0 end else case "eidhpar"."Xrevstiko" when 1 then case when "eidhpar"."Emf_timvn" <> 2 then "grammes"."Posothta" else 0 end else 0 end end ) ),2) AS varchar(100)) from "grammes","pvlhseis","eidhpar","tameia" tam where cast("pvlhseis"."Hmeromhnia" as date)<= 'NOW' and tam."Aa" in (40,36) and "apouhkh"."Aa"="grammes"."Eidos" and "grammes"."Aapar" = "pvlhseis"."Aa" and "pvlhseis"."Parastatiko" = "eidhpar"."Aa" and tam."Aa"="eidhpar"."Tameio" and ("KvdikosEidoys" <> '') AND ("KvdikosEidoys" is not null)) , "custom6"= (select cast(round( ( sum(case "eidhpar"."Xrevstiko" when 0 then case when "eidhpar"."Emf_timvn" <> 2 then "grammes"."Posothta" else 0 end else case "eidhpar"."Xrevstiko" when 1 then case when "eidhpar"."Emf_timvn" = 2 then -"grammes"."Posothta" else 0 end else 0 end end ) - sum (case "eidhpar" . "Xrevstiko" when 0 then case when "eidhpar"."Emf_timvn" = 2 then -"grammes"."Posothta" else 0 end else case "eidhpar"."Xrevstiko" when 1 then case when "eidhpar"."Emf_timvn" <> 2 then "grammes"."Posothta" else 0 end else 0 end end ) ),2) AS varchar(100)) from "grammes","pvlhseis","eidhpar","tameia" tam where cast("pvlhseis"."Hmeromhnia" as date)<= 'NOW' and tam."Aa" in (1,20) and "apouhkh"."Aa"="grammes"."Eidos" and "grammes"."Aapar" = "pvlhseis"."Aa" and "pvlhseis"."Parastatiko" = "eidhpar"."Aa" and tam."Aa"="eidhpar"."Tameio" and ("KvdikosEidoys" <> '') AND ("KvdikosEidoys" is not null)) WHERE "apouhkh"."Aa" IN (SELECT "grammes"."Eidos" FROM "grammes","pvlhseis" WHERE "grammes"."Aapar"="pvlhseis"."Aa" AND "pvlhseis"."modified">#MODIFIED) |
