Loyalties script

The script must be pasted on the corresponding document at:

Settings->Purchase/sales->Documents->Run SQL

Indicative for use for loyalties collection feature copy and the following code to receipt:

EXECUTE BLOCK AS BEGIN

--IF CUSTOMER IS RETAIL THEN EXIT
if ((select "pelates"."Epvnymia" from "pelates" where "pelates"."Aa"=:cl) not like '%RETAIL%') THEN BEGIN
                                        

                  
                                                            

                                                                                        

    if ((select count("pvlhseis"."Aa") from "pvlhseis" where "pvlhseis"."Aa"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa))>0) then begin
                                                                                     

        --UPDATE DOCUMENT LINE INCLUDED ONE PRODUCT WITH ID 'LOY'
        update "grammes" set
        "grammes"."Aa"=(select gen_id("gen_grammes_id", 1) as fname from RDB$DATABASE),
        "grammes"."Aapar"=(select pvl1."Aa" from "pvlhseis" pvl1 where pvl1."Sxetika" like '%&'||:aa),
        "grammes"."Eidos"=(select apo1."Aa" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        "grammes"."KvdikosEidoys"=(select apo1."Kvdikos" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        "grammes"."PerigrafhEidoys"=(select apo1."Perigrafh" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        "grammes"."fpa_"=(select apo1."fpaT" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        "grammes"."Posothta"=(select "pvlhseis"."synolo_" from "pvlhseis" where "pvlhseis"."Aa"=:aa)
                                                                                               
        -coalesce((select sum(ggr."Posothta")  from "pvlhseis","grammes" ggr where "pvlhseis"."Aa"=:aa
         and ggr."Aapar"="pvlhseis"."Aa" and ggr."KvdikosEidoys"='LOYPAY'),0)
        ,
        "grammes"."Monada"=(select apo1."Monada" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        "grammes"."monada_"=(select "monades"."Monades" from "apouhkh" apo1 left join "monades" on "monades"."Aa"=apo1."Monada" where apo1."Kvdikos"='LOY'),
        "grammes"."Timh"=(select apo1."Lianikh" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        "grammes"."Ekptvsh"=0,
        "grammes"."order"=0

        where "grammes"."Aapar"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa)
                                                                                                  

        ;

    end else begin
                           
        --INCREMENT NUMBER TO DOCUMENT TYPE WITH LINE ID 57
        update "eidhpar" set "eidhpar"."Ariumos"="eidhpar"."Ariumos"+1 where "eidhpar"."Aa"= 57

        ;

        --CREATE NEW DOCUMENT WITH LINE ID 57
        insert into "pvlhseis" ("pvlhseis"."Aa", "pvlhseis"."Ariumospar","pvlhseis"."Kvdikospelath",
        "pvlhseis"."Hmeromhnia","pvlhseis"."Parastatiko","pvlhseis"."Sxetika"  )
        values (
        (select gen_id("gen_pvlhseis_id", 1) as fname from RDB$DATABASE),
        (select eid1."Ariumos" from "eidhpar" eid1 where eid1."Aa"=57),
        (select pvl1."Kvdikospelath" from "pvlhseis" pvl1 where pvl1."Aa"=:aa),
        'NOW',57,
        (select substring(eid2."Parastatiko" from 1 for 3)||' '||pvl2."Seira"||'#'||pvl2."Ariumospar" from "pvlhseis" pvl2
        left join "eidhpar" eid2 on eid2."Aa"=pvl2."Parastatiko"
        where pvl2."Aa"=:aa)||' &'||:aa
        )
    
        ;
                                   
    
        --CREATE NEW DOCUMENT LINE INCLUDED ONE PRODUCT WITH ID 'LOY'
        insert into "grammes" ("grammes"."Aa","grammes"."Aapar","grammes"."Eidos","grammes"."KvdikosEidoys",
        "grammes"."PerigrafhEidoys","grammes"."fpa_","grammes"."Posothta","grammes"."Monada","grammes"."monada_",
        "grammes"."Timh","grammes"."Ekptvsh","grammes"."order"
        )
        values(
        (select gen_id("gen_grammes_id", 1) as fname from RDB$DATABASE),
        (select pvl1."Aa" from "pvlhseis" pvl1 where pvl1."Sxetika" like '%&'||:aa),
        
        (select apo1."Aa" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        (select apo1."Kvdikos" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        (select apo1."Perigrafh" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        (select apo1."fpaT" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        (select floor("pvlhseis"."synolo_") from "pvlhseis" where "pvlhseis"."Aa"=:aa)

        -(select coalesce(sum(ggr."Posothta"),0)  from "pvlhseis","grammes" ggr where "pvlhseis"."Aa"=:aa
         and ggr."Aapar"="pvlhseis"."Aa" and ggr."KvdikosEidoys"='LOYPAY')

        
        ,
        (select apo1."Monada" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        (select "monades"."Monades" from "apouhkh" apo1 left join "monades" on "monades"."Aa"=apo1."Monada" where apo1."Kvdikos"='LOY'),
        (select apo1."Lianikh" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        0,0
        )
    
        ;
    end                            




--UPDATE NEW DOCUMENT ADDING TOTALS
update "pvlhseis" pvm set                                 

pvm."synolofpa_"=
(select
    (((gra ."Posothta"* gra."Timh")* gra."fpa_"   )/100) from
"grammes" gra where gra."Aapar"=pvm."Aa"
),

pvm."synolonpe_"=
(select
    (((gra ."Posothta"* gra."Timh")* gra."fpa_"   )/100) from
"grammes" gra where gra."Aapar"=pvm."Aa"
),

pvm."synolo_"=
(select (gra ."Posothta"* gra."Timh")
 +    (((gra ."Posothta"* gra."Timh")* gra."fpa_"   )/100) from
"grammes" gra where gra."Aapar"=pvm."Aa"
),

pvm."synoloposothtas_"=
(select floor(gra ."Posothta") from
"grammes" gra where gra."Aapar"=pvm."Aa"
)                                 


        -(select coalesce(sum(ggr."Posothta"),0)  from "pvlhseis","grammes" ggr where "pvlhseis"."Aa"=pvm."Aa"
         and ggr."Aapar"="pvlhseis"."Aa" and ggr."KvdikosEidoys"='LOYPAY')


,
pvm."ogkos_syn"=0,pvm."embado_syn"=0,pvm."baros_syn"=0,pvm."ajia_syn"=0,pvm."synolikopososto"=0,pvm."loipes_"=0
where pvm."Aa"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa)

;

/*STORE in CUSTOM FIELD of the current document QUANTITY|VALUE when DOCUMENT TYPE WITH LINE ID 57*/
update "pvlhseis" set "pvlhseis"."custom"=

(select sum(floor(cast(pvl2."synoloposothtas_" as int)))  from "pvlhseis" pvl2 where pvl2."Parastatiko"=57
and pvl2."Kvdikospelath"= :cl)
||'|'||
(select cast(floor(pvl."synolo_") as int)  from "pvlhseis" pvl where pvl."Aa"=:aa)
||'|'||
round((select sum(cast(pvl2."synoloposothtas_" as int)) from "pvlhseis" pvl2 where pvl2."Parastatiko"=57
and pvl2."Kvdikospelath"= :cl)
-
(select floor(pvl."synolo_") from "pvlhseis" pvl where pvl."Aa"=:aa))

where "pvlhseis"."Aa"=:aa
;

END


END;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

EXECUTE BLOCK AS BEGIN

--IF CUSTOMER IS RETAIL THEN EXIT

if ((select "pelates"."Epvnymia" from "pelates" where "pelates"."Aa"=:cl) not like '%RETAIL%') THEN BEGIN

if ((select count("pvlhseis"."Aa") from "pvlhseis" where "pvlhseis"."Aa"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa))>0) then begin

--UPDATE DOCUMENT LINE INCLUDED ONE PRODUCT WITH ID 'LOY'

update "grammes" set

"grammes"."Aa"=(select gen_id("gen_grammes_id", 1) as fname from RDB$DATABASE),

"grammes"."Aapar"=(select pvl1."Aa" from "pvlhseis" pvl1 where pvl1."Sxetika" like '%&'||:aa),

"grammes"."Eidos"=(select apo1."Aa" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

"grammes"."KvdikosEidoys"=(select apo1."Kvdikos" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

"grammes"."PerigrafhEidoys"=(select apo1."Perigrafh" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

"grammes"."fpa_"=(select apo1."fpaT" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

"grammes"."Posothta"=(select "pvlhseis"."synolo_" from "pvlhseis" where "pvlhseis"."Aa"=:aa)

-coalesce((select sum(ggr."Posothta") from "pvlhseis","grammes" ggr where "pvlhseis"."Aa"=:aa

and ggr."Aapar"="pvlhseis"."Aa" and ggr."KvdikosEidoys"='LOYPAY'),0)

"grammes"."Monada"=(select apo1."Monada" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

"grammes"."monada_"=(select "monades"."Monades" from "apouhkh" apo1 left join "monades" on "monades"."Aa"=apo1."Monada" where apo1."Kvdikos"='LOY'),

"grammes"."Timh"=(select apo1."Lianikh" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

"grammes"."Ekptvsh"=0,

"grammes"."order"=0

where "grammes"."Aapar"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa)

;

end else begin

--INCREMENT NUMBER TO DOCUMENT TYPE WITH LINE ID 57

update "eidhpar" set "eidhpar"."Ariumos"="eidhpar"."Ariumos"+1 where "eidhpar"."Aa"= 57

;

--CREATE NEW DOCUMENT WITH LINE ID 57

insert into "pvlhseis" ("pvlhseis"."Aa", "pvlhseis"."Ariumospar","pvlhseis"."Kvdikospelath",

"pvlhseis"."Hmeromhnia","pvlhseis"."Parastatiko","pvlhseis"."Sxetika" )

values (

(select gen_id("gen_pvlhseis_id", 1) as fname from RDB$DATABASE),

(select eid1."Ariumos" from "eidhpar" eid1 where eid1."Aa"=57),

(select pvl1."Kvdikospelath" from "pvlhseis" pvl1 where pvl1."Aa"=:aa),

'NOW',57,

(select substring(eid2."Parastatiko" from 1 for 3)||' '||pvl2."Seira"||'#'||pvl2."Ariumospar" from "pvlhseis" pvl2

left join "eidhpar" eid2 on eid2."Aa"=pvl2."Parastatiko"

where pvl2."Aa"=:aa)||' &'||:aa

)

;

--CREATE NEW DOCUMENT LINE INCLUDED ONE PRODUCT WITH ID 'LOY'

insert into "grammes" ("grammes"."Aa","grammes"."Aapar","grammes"."Eidos","grammes"."KvdikosEidoys",

"grammes"."PerigrafhEidoys","grammes"."fpa_","grammes"."Posothta","grammes"."Monada","grammes"."monada_",

"grammes"."Timh","grammes"."Ekptvsh","grammes"."order"

)

values(

(select gen_id("gen_grammes_id", 1) as fname from RDB$DATABASE),

(select pvl1."Aa" from "pvlhseis" pvl1 where pvl1."Sxetika" like '%&'||:aa),

(select apo1."Aa" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

(select apo1."Kvdikos" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

(select apo1."Perigrafh" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

(select apo1."fpaT" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

(select floor("pvlhseis"."synolo_") from "pvlhseis" where "pvlhseis"."Aa"=:aa)

-(select coalesce(sum(ggr."Posothta"),0) from "pvlhseis","grammes" ggr where "pvlhseis"."Aa"=:aa

and ggr."Aapar"="pvlhseis"."Aa" and ggr."KvdikosEidoys"='LOYPAY')

(select apo1."Monada" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

(select "monades"."Monades" from "apouhkh" apo1 left join "monades" on "monades"."Aa"=apo1."Monada" where apo1."Kvdikos"='LOY'),

(select apo1."Lianikh" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

0,0

)

;

end

--UPDATE NEW DOCUMENT ADDING TOTALS

update "pvlhseis" pvm set

pvm."synolofpa_"=

(select

(((gra ."Posothta"* gra."Timh")* gra."fpa_" )/100) from

"grammes" gra where gra."Aapar"=pvm."Aa"

pvm."synolonpe_"=

(select

(((gra ."Posothta"* gra."Timh")* gra."fpa_" )/100) from

"grammes" gra where gra."Aapar"=pvm."Aa"

pvm."synolo_"=

(select (gra ."Posothta"* gra."Timh")

+ (((gra ."Posothta"* gra."Timh")* gra."fpa_" )/100) from

"grammes" gra where gra."Aapar"=pvm."Aa"

pvm."synoloposothtas_"=

(select floor(gra ."Posothta") from

"grammes" gra where gra."Aapar"=pvm."Aa"

)

-(select coalesce(sum(ggr."Posothta"),0) from "pvlhseis","grammes" ggr where "pvlhseis"."Aa"=pvm."Aa"

and ggr."Aapar"="pvlhseis"."Aa" and ggr."KvdikosEidoys"='LOYPAY')

pvm."ogkos_syn"=0,pvm."embado_syn"=0,pvm."baros_syn"=0,pvm."ajia_syn"=0,pvm."synolikopososto"=0,pvm."loipes_"=0

where pvm."Aa"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa)

;

/*STORE in CUSTOM FIELD of the current document QUANTITY|VALUE when DOCUMENT TYPE WITH LINE ID 57*/

update "pvlhseis" set "pvlhseis"."custom"=

(select sum(floor(cast(pvl2."synoloposothtas_" as int))) from "pvlhseis" pvl2 where pvl2."Parastatiko"=57

and pvl2."Kvdikospelath"= :cl)

||'|'||

(select cast(floor(pvl."synolo_") as int) from "pvlhseis" pvl where pvl."Aa"=:aa)

||'|'||

round((select sum(cast(pvl2."synoloposothtas_" as int)) from "pvlhseis" pvl2 where pvl2."Parastatiko"=57

and pvl2."Kvdikospelath"= :cl)

(select floor(pvl."synolo_") from "pvlhseis" pvl where pvl."Aa"=:aa))

where "pvlhseis"."Aa"=:aa

;

END

END;

and the following to return:

EXECUTE BLOCK AS BEGIN

--IF CUSTOMER IS RETAIL THEN EXIT
if ((select "pelates"."Epvnymia" from "pelates" where "pelates"."Aa"=:cl) not like '%RETAIL%') THEN BEGIN
                                        


                                                            

                                                                                        

    if ((select count("pvlhseis"."Aa") from "pvlhseis" where "pvlhseis"."Aa"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa))>0) then begin
                                                                                     

        --UPDATE DOCUMENT LINE INCLUDED ONE PRODUCT WITH ID 'LOY'
        update "grammes" set
        "grammes"."Aa"=(select gen_id("gen_grammes_id", 1) as fname from RDB$DATABASE),
        "grammes"."Aapar"=(select pvl1."Aa" from "pvlhseis" pvl1 where pvl1."Sxetika" like '%&'||:aa),
        "grammes"."Eidos"=(select apo1."Aa" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        "grammes"."KvdikosEidoys"=(select apo1."Kvdikos" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        "grammes"."PerigrafhEidoys"=(select apo1."Perigrafh" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),     
        "grammes"."fpa_"=(select apo1."fpaT" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        "grammes"."Posothta"=-(select "pvlhseis"."synolo_" from "pvlhseis" where "pvlhseis"."Aa"=:aa)
                                                                                               
        -coalesce((select sum(ggr."Posothta")  from "pvlhseis","grammes" ggr where "pvlhseis"."Aa"=:aa
         and ggr."Aapar"="pvlhseis"."Aa" and ggr."KvdikosEidoys"='LOYPAY'),0)
        ,
        "grammes"."Monada"=(select apo1."Monada" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        "grammes"."monada_"=(select "monades"."Monades" from "apouhkh" apo1 left join "monades" on "monades"."Aa"=apo1."Monada" where apo1."Kvdikos"='LOY'),
        "grammes"."Timh"=(select apo1."Lianikh" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        "grammes"."Ekptvsh"=0,
        "grammes"."order"=0

        where "grammes"."Aapar"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa)
                                                                                                  
                                             
        ;

    end else begin
                           
        --INCREMENT NUMBER TO DOCUMENT TYPE WITH LINE ID 57
        update "eidhpar" set "eidhpar"."Ariumos"="eidhpar"."Ariumos"+1 where "eidhpar"."Aa"= 57

        ;

        --CREATE NEW DOCUMENT WITH LINE ID 57
        insert into "pvlhseis" ("pvlhseis"."Aa", "pvlhseis"."Ariumospar","pvlhseis"."Kvdikospelath",
        "pvlhseis"."Hmeromhnia","pvlhseis"."Parastatiko","pvlhseis"."Sxetika"  )
        values (
        (select gen_id("gen_pvlhseis_id", 1) as fname from RDB$DATABASE),
        (select eid1."Ariumos" from "eidhpar" eid1 where eid1."Aa"=57),
        (select pvl1."Kvdikospelath" from "pvlhseis" pvl1 where pvl1."Aa"=:aa),
        'NOW',57,
        (select substring(eid2."Parastatiko" from 1 for 3)||' '||pvl2."Seira"||'#'||pvl2."Ariumospar" from "pvlhseis" pvl2
        left join "eidhpar" eid2 on eid2."Aa"=pvl2."Parastatiko"
        where pvl2."Aa"=:aa)||' &'||:aa
        )
    
        ;
                                   
    
        --CREATE NEW DOCUMENT LINE INCLUDED ONE PRODUCT WITH ID 'LOY'
        insert into "grammes" ("grammes"."Aa","grammes"."Aapar","grammes"."Eidos","grammes"."KvdikosEidoys",
        "grammes"."PerigrafhEidoys","grammes"."fpa_","grammes"."Posothta","grammes"."Monada","grammes"."monada_",
        "grammes"."Timh","grammes"."Ekptvsh","grammes"."order"
        )
        values(
        (select gen_id("gen_grammes_id", 1) as fname from RDB$DATABASE),
        (select pvl1."Aa" from "pvlhseis" pvl1 where pvl1."Sxetika" like '%&'||:aa),
                                          
        (select apo1."Aa" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        (select apo1."Kvdikos" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        (select apo1."Perigrafh" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        (select apo1."fpaT" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        -(select floor("pvlhseis"."synolo_") from "pvlhseis" where "pvlhseis"."Aa"=:aa)

        -(select coalesce(sum(ggr."Posothta"),0)  from "pvlhseis","grammes" ggr where "pvlhseis"."Aa"=:aa
         and ggr."Aapar"="pvlhseis"."Aa" and ggr."KvdikosEidoys"='LOYPAY')

        
        ,
        (select apo1."Monada" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        (select "monades"."Monades" from "apouhkh" apo1 left join "monades" on "monades"."Aa"=apo1."Monada" where apo1."Kvdikos"='LOY'),
        (select apo1."Lianikh" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),
        0,0
        )
    
        ;
    end                                 


                       

--UPDATE NEW DOCUMENT ADDING TOTALS
update "pvlhseis" pvm set                                 

pvm."synolofpa_"=
(select
    (((gra ."Posothta"* gra."Timh")* gra."fpa_"   )/100) from
"grammes" gra where gra."Aapar"=pvm."Aa"
),

pvm."synolonpe_"=
(select
    (((gra ."Posothta"* gra."Timh")* gra."fpa_"   )/100) from
"grammes" gra where gra."Aapar"=pvm."Aa"
),

pvm."synolo_"=
(select (gra ."Posothta"* gra."Timh")
 +    (((gra ."Posothta"* gra."Timh")* gra."fpa_"   )/100) from
"grammes" gra where gra."Aapar"=pvm."Aa"
),

pvm."synoloposothtas_"=-
(select floor(gra ."Posothta") from
"grammes" gra where gra."Aapar"=pvm."Aa"
)                                 


        -(select coalesce(sum(ggr."Posothta"),0)  from "pvlhseis","grammes" ggr where "pvlhseis"."Aa"=pvm."Aa"
         and ggr."Aapar"="pvlhseis"."Aa" and ggr."KvdikosEidoys"='LOYPAY')


,
pvm."ogkos_syn"=0,pvm."embado_syn"=0,pvm."baros_syn"=0,pvm."ajia_syn"=0,pvm."synolikopososto"=0,pvm."loipes_"=0
where pvm."Aa"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa)

;

/*STORE in CUSTOM FIELD of the current document QUANTITY|VALUE when DOCUMENT TYPE WITH LINE ID 57*/
update "pvlhseis" set "pvlhseis"."custom"=

(select sum(floor(cast(pvl2."synoloposothtas_" as int)))  from "pvlhseis" pvl2 where pvl2."Parastatiko"=57
and pvl2."Kvdikospelath"= :cl)
||'|'||
(select cast(floor(pvl."synolo_") as int)  from "pvlhseis" pvl where pvl."Aa"=:aa)
||'|'||
round((select sum(cast(pvl2."synoloposothtas_" as int)) from "pvlhseis" pvl2 where pvl2."Parastatiko"=57
and pvl2."Kvdikospelath"= :cl)
-
(select floor(pvl."synolo_") from "pvlhseis" pvl where pvl."Aa"=:aa))

where "pvlhseis"."Aa"=:aa
;

END


END;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

EXECUTE BLOCK AS BEGIN

--IF CUSTOMER IS RETAIL THEN EXIT

if ((select "pelates"."Epvnymia" from "pelates" where "pelates"."Aa"=:cl) not like '%RETAIL%') THEN BEGIN

if ((select count("pvlhseis"."Aa") from "pvlhseis" where "pvlhseis"."Aa"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa))>0) then begin

--UPDATE DOCUMENT LINE INCLUDED ONE PRODUCT WITH ID 'LOY'

update "grammes" set

"grammes"."Aa"=(select gen_id("gen_grammes_id", 1) as fname from RDB$DATABASE),

"grammes"."Aapar"=(select pvl1."Aa" from "pvlhseis" pvl1 where pvl1."Sxetika" like '%&'||:aa),

"grammes"."Eidos"=(select apo1."Aa" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

"grammes"."KvdikosEidoys"=(select apo1."Kvdikos" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

"grammes"."PerigrafhEidoys"=(select apo1."Perigrafh" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

"grammes"."fpa_"=(select apo1."fpaT" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

"grammes"."Posothta"=-(select "pvlhseis"."synolo_" from "pvlhseis" where "pvlhseis"."Aa"=:aa)

-coalesce((select sum(ggr."Posothta") from "pvlhseis","grammes" ggr where "pvlhseis"."Aa"=:aa

and ggr."Aapar"="pvlhseis"."Aa" and ggr."KvdikosEidoys"='LOYPAY'),0)

"grammes"."Monada"=(select apo1."Monada" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

"grammes"."monada_"=(select "monades"."Monades" from "apouhkh" apo1 left join "monades" on "monades"."Aa"=apo1."Monada" where apo1."Kvdikos"='LOY'),

"grammes"."Timh"=(select apo1."Lianikh" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

"grammes"."Ekptvsh"=0,

"grammes"."order"=0

where "grammes"."Aapar"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa)

;

end else begin

--INCREMENT NUMBER TO DOCUMENT TYPE WITH LINE ID 57

update "eidhpar" set "eidhpar"."Ariumos"="eidhpar"."Ariumos"+1 where "eidhpar"."Aa"= 57

;

--CREATE NEW DOCUMENT WITH LINE ID 57

insert into "pvlhseis" ("pvlhseis"."Aa", "pvlhseis"."Ariumospar","pvlhseis"."Kvdikospelath",

"pvlhseis"."Hmeromhnia","pvlhseis"."Parastatiko","pvlhseis"."Sxetika" )

values (

(select gen_id("gen_pvlhseis_id", 1) as fname from RDB$DATABASE),

(select eid1."Ariumos" from "eidhpar" eid1 where eid1."Aa"=57),

(select pvl1."Kvdikospelath" from "pvlhseis" pvl1 where pvl1."Aa"=:aa),

'NOW',57,

(select substring(eid2."Parastatiko" from 1 for 3)||' '||pvl2."Seira"||'#'||pvl2."Ariumospar" from "pvlhseis" pvl2

left join "eidhpar" eid2 on eid2."Aa"=pvl2."Parastatiko"

where pvl2."Aa"=:aa)||' &'||:aa

)

;

--CREATE NEW DOCUMENT LINE INCLUDED ONE PRODUCT WITH ID 'LOY'

insert into "grammes" ("grammes"."Aa","grammes"."Aapar","grammes"."Eidos","grammes"."KvdikosEidoys",

"grammes"."PerigrafhEidoys","grammes"."fpa_","grammes"."Posothta","grammes"."Monada","grammes"."monada_",

"grammes"."Timh","grammes"."Ekptvsh","grammes"."order"

)

values(

(select gen_id("gen_grammes_id", 1) as fname from RDB$DATABASE),

(select pvl1."Aa" from "pvlhseis" pvl1 where pvl1."Sxetika" like '%&'||:aa),

(select apo1."Aa" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

(select apo1."Kvdikos" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

(select apo1."Perigrafh" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

(select apo1."fpaT" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

-(select floor("pvlhseis"."synolo_") from "pvlhseis" where "pvlhseis"."Aa"=:aa)

-(select coalesce(sum(ggr."Posothta"),0) from "pvlhseis","grammes" ggr where "pvlhseis"."Aa"=:aa

and ggr."Aapar"="pvlhseis"."Aa" and ggr."KvdikosEidoys"='LOYPAY')

(select apo1."Monada" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

(select "monades"."Monades" from "apouhkh" apo1 left join "monades" on "monades"."Aa"=apo1."Monada" where apo1."Kvdikos"='LOY'),

(select apo1."Lianikh" from "apouhkh" apo1 where apo1."Kvdikos"='LOY'),

0,0

)

;

end

--UPDATE NEW DOCUMENT ADDING TOTALS

update "pvlhseis" pvm set

pvm."synolofpa_"=

(select

(((gra ."Posothta"* gra."Timh")* gra."fpa_" )/100) from

"grammes" gra where gra."Aapar"=pvm."Aa"

pvm."synolonpe_"=

(select

(((gra ."Posothta"* gra."Timh")* gra."fpa_" )/100) from

"grammes" gra where gra."Aapar"=pvm."Aa"

pvm."synolo_"=

(select (gra ."Posothta"* gra."Timh")

+ (((gra ."Posothta"* gra."Timh")* gra."fpa_" )/100) from

"grammes" gra where gra."Aapar"=pvm."Aa"

pvm."synoloposothtas_"=-

(select floor(gra ."Posothta") from

"grammes" gra where gra."Aapar"=pvm."Aa"

)

-(select coalesce(sum(ggr."Posothta"),0) from "pvlhseis","grammes" ggr where "pvlhseis"."Aa"=pvm."Aa"

and ggr."Aapar"="pvlhseis"."Aa" and ggr."KvdikosEidoys"='LOYPAY')

pvm."ogkos_syn"=0,pvm."embado_syn"=0,pvm."baros_syn"=0,pvm."ajia_syn"=0,pvm."synolikopososto"=0,pvm."loipes_"=0

where pvm."Aa"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa)

;

/*STORE in CUSTOM FIELD of the current document QUANTITY|VALUE when DOCUMENT TYPE WITH LINE ID 57*/

update "pvlhseis" set "pvlhseis"."custom"=

(select sum(floor(cast(pvl2."synoloposothtas_" as int))) from "pvlhseis" pvl2 where pvl2."Parastatiko"=57

and pvl2."Kvdikospelath"= :cl)

||'|'||

(select cast(floor(pvl."synolo_") as int) from "pvlhseis" pvl where pvl."Aa"=:aa)

||'|'||

round((select sum(cast(pvl2."synoloposothtas_" as int)) from "pvlhseis" pvl2 where pvl2."Parastatiko"=57

and pvl2."Kvdikospelath"= :cl)

(select floor(pvl."synolo_") from "pvlhseis" pvl where pvl."Aa"=:aa))

where "pvlhseis"."Aa"=:aa

;

END

END;