/*
--Parametri
wbvardef commessa=421335    -- Commessa da analizzare        
;
wbvardef dtelab=28/02/2023  -- Data analisi
;

--Output totali
select dtelab, comm, 'R' ord, cls, sum(costo)
from t_costicom
where comm = '$[commessa]'
and dtelab = '$[dtelab]'
and liv = 0
and tpcls = 0 
group by 1,2,3,4
union all
select dtelab, comm, 'T', 'TOT' cls, sum(costo)
from t_costicom
where comm = '$[commessa]'
and dtelab = '$[dtelab]'
and liv = 0
and tpcls = 0 
group by 1,2,3,4
order by 1,2,3 desc,4
;

*/

WbSetProp workbench.sql.log.statements=true;
set autocommit on;
set isolation to dirty read;
wbecho commessa=$[commessa];
wbecho dtelab = $[dtelab];
/*
Da verificare:
  . costi generici (ora articoli non a giacenza ma prelevati da ODL)

Mancano (se servissero):
  . costo figli non in LDP (leggere solo costimag)
  . costo fasi non rilevate (leggere da mpfaseol in nvl)

--Parametri
wbvardef commessa=421335    -- Commessa da analizzare        
;
wbvardef dtelab=28/02/2023  -- Data analisi
;

*/

--Configurazione
wbvardef tipocosto=@"select valore from sxparams where cod_azienda = 0 and cod_param = 'CO_TIPOCOSTO'"
;
wbvardef tipocostohh=@"select valore from sxparams where cod_azienda = 0 and cod_param = 'CO_TIPOCOSTOHH'"
;
wbvardef val_acqcomm=@"select valore from sxparams where cod_azienda = 0 and cod_param = 'CO_VALACQCOMM'"
;
wbvardef vocegen=@"select valore from sxparams where cod_azienda = 0 and cod_param = 'CO_VOCEGEN'"
;
wbvardef voceext=@"select valore from sxparams where cod_azienda = 0 and cod_param = 'CO_VOCEEXT'"
;
wbvardef voceffp=@"select valore from sxparams where cod_azienda = 0 and cod_param = 'CO_VOCEFFP'"
;
wbvardef tariffahhodl=@"select valore from sxparams where cod_azienda = 0 and cod_param = 'CO_TARIFFAHHOL'"
;
wbvardef tariffahhgen=@"select valore from sxparams where cod_azienda = 0 and cod_param = 'CO_TARIFFAHHGE'"
;
wbvardef vocetot=@"select mpp_voct from mpparam"
;
wbvardef base_tempi=@"select case when mpp_bast = 'M' then 60 else 1 end from mpparam"
;
wbvardef frazione_tempi=@"select case when mpp_ntem = 'C' then 100 else 60 end from mpparam"
;

--Estrazione di tutti i movimenti di magazzino associati alla commessa
/*
wbexport -type=xlsx -file=$[path]\$[filename].xlsx -title=movimenti -fixedDigits=2
;
select '$[dtelab]' dtelab, nvl(gim_comm,blt_comm) comm, gim_sotc sotc, nvl(gim_tord,blt_tord) tord, nvl(gim_nord, blt_nord) nord, gim_data data, 
gim_code nrmov, gim_riga riga, blt_fc-blt_fs cs, 
gim_arti arti, amg_dest descrizione, blt_tipo caus, blt_cocl fornitore, des_clifor denominazione, gim_qmov qta, gim_preu preu, 
case when blt_fcof = 'F' then (UVL(gim_qmov)) * NVL(gim_preu,0) * Zvl(gim_qext) / Uvl(gim_qmov) * UVL(blt_cambe) / UVL(blt_camb) 
    * (100 - NVL(gim_sco1,0)) / 100 * (100 - NVL(gim_sco2,0)) / 100 * (100 - NVL(gim_sco3,0)) / 100
else gim_qmov*gim_preu end imprig
from mggior, bfbolt, outer agclifor, mgcausa, mganag
where gim_code = blt_code 
and blt_tipo = cmg_code
and gim_arti = amg_code
and blt_cocl = cod_clifor
--and amg_tcom = 'P'
and gim_comm = '$[commessa]'
and gim_data <= '$[dtelab]'
and gim_depo = 1
--and nvl(amg_fagi ,'S') = 'S'
;
*/
/*
drop table tmp_costiodlmat
;
select gim_comm comm, gim_sotc sotc, gim_nord odl, gim_rord riga, gim_arti arti, amg_vcma voce, blt_tipo caus, gim_qmov qta, gim_qmov *
nvl((select nvl(tc.cod_vali,0) + nvl(tc.cod_vald,0)
    from mpcosdat tc
   where tc.cod_arti = amg_code
     and tc.cod_arti = amg_code
     and tc.cod_tipo = '$[tipocosto]' and tc.cod_voce = amg_vcma
     and tc.cod_depo = amg_depp and tc.cod_fap = amg_tipo
     and tc.cod_data = (select max(tc2.cod_data)
    from mpcosdat tc2 where tc2.cod_arti = tc.cod_arti
    and tc2.cod_tipo = '$[tipocosto]' and tc2.cod_voce = amg_vcma
    and tc2.cod_depo = amg_depp and tc2.cod_fap = amg_tipo
    and tc2.cod_data <= last_day(gim_data))
  ),
(select coa_vald+coa_vali from mpcosti where coa_arti = amg_code and coa_depo = amg_depp 
 and coa_fap = amg_tipo and coa_tipo = '$[tipocosto]' and coa_voce = amg_vcma)) costo
from mggior, bfbolt, mgcausa, mganag
where gim_code = blt_code 
and blt_tipo = cmg_code
and gim_arti = amg_code
--and amg_tcom = 'P'
--and nvl(amg_fagi ,'S') = 'S'
and gim_comm = '$[commessa]'
and gim_data <= '$[dtelab]'
and gim_depo = 1
and gim_nord is not null
and gim_tord is null
and blt_fs = 1
and cmg_fcof = 'P'
into temp tmp_costiodlmat
;
--Estrazione costo materiali per odl
wbexport -type=xlsx -file=$[path]\$[filename].xlsx -title=costomateriali_odl -fixedDigits=2 -append=true
;
select odl, mol_parte articolo, amg_dest descrizione, mol_stato stato, voce, sum(qta) qta, sum(costo) costo
from tmp_costiodlmat, mpordil, mganag
where odl = mol_code
and mol_parte = amg_code
group by 1,2,3,4,5
order by 1,2,4,5
;
*/
drop table t_costimat0
;
select gim_comm comm, gim_sotc sotc, gim_arti arti, '$[vocetot]' voce, amg_llcd llcd, amg_tipo ap,
nvl((select nvl(tc.cod_vali,0) + nvl(tc.cod_vald,0)
    from mpcosdat tc
   where tc.cod_arti = amg_code
     and tc.cod_arti = amg_code
     and tc.cod_tipo = '$[tipocosto]' and tc.cod_voce = '$[vocetot]'
     and tc.cod_depo = amg_depp and tc.cod_fap = amg_tipo
     and tc.cod_data = (select max(tc2.cod_data)
    from mpcosdat tc2 where tc2.cod_arti = tc.cod_arti
    and tc2.cod_tipo = '$[tipocosto]' and tc2.cod_voce = '$[vocetot]'
    and tc2.cod_depo = amg_depp and tc2.cod_fap = amg_tipo
    and tc2.cod_data <= last_day(gim_data))
  ),
(select coa_vald+coa_vali from mpcosti where coa_arti = amg_code and coa_depo = amg_depp 
 and coa_fap = amg_tipo and coa_tipo = '$[tipocosto]' and coa_voce = '$[vocetot]')) costomag,
(select sum(ofc_preu*ofc_qext)/sum(ofc_qord) 
 from ofordic, ofordit, oftipord 
 where ofc_tipo = oft_tipo and ofc_code = oft_code and oft_tofo = oto_code
 --and nvl(oto_tipo,'.') <> 'E'--
 and not (oft_stat = 'C' and ofc_qcon = 0)
 and ofc_arti = gim_arti and ofc_comm = '$[commessa]') acqcomm,
/*
 case when blt_fcof = 'F' then NVL(gim_preu,0) * Zvl(gim_qext) / Uvl(gim_qmov) * UVL(blt_cambe) / UVL(blt_camb) 
     * (100 - NVL(gim_sco1,0)) / 100 * (100 - NVL(gim_sco2,0)) / 100 * (100 - NVL(gim_sco3,0)) / 100
 else gim_preu end acqcomm,
*/
-- sum(case when (blt_fcof = 'P' or cmg_fter = 'S') and blt_fs = 1 then gim_qmov else 0 end) kpre, 
-- sum(case when (blt_fcof = 'P' or cmg_fter = 'S') and blt_fc = 1 then gim_qmov else 0 end) kver, 
 sum(case when (blt_fcof = 'P' or cmg_fter = 'S') and blt_fs = 1 then gim_qmov else 0 end) kpre, 
 sum(case when (blt_fcof = 'P' or cmg_fter = 'S') and blt_fc = 1 then gim_qmov else 0 end) kver, 
 sum(gim_qmov * blt_fs) ksca, 
 sum(gim_qmov * blt_fc) kcar, 
 --sum(gim_qmov * (blt_fc-blt_fs)) kgia,
 max(cmg_fter) fter
from mggior, bfbolt, mgcausa, mganag
where gim_code = blt_code 
and blt_tipo = cmg_code
and gim_arti = amg_code
--and amg_tcom = 'P'
--and nvl(amg_fagi ,'S') = 'S'
and gim_comm = '$[commessa]'
and gim_data <= '$[dtelab]'
----------------------------------------and nvl(cmg_fter,'N') <> 'S'
----and gim_depo = 1
group by 1,2,3,4,5,6,7,8
into temp t_costimat0
;
/*
update t_costimat0 
set costomag = 0, acqcomm = 0 
where fter = 'S'
;
update t_costimat0 
set kgia = 0 
where kgia < 0
;
*/
update t_costimat0
set acqcomm = costomag
where '$[val_acqcomm]' <> 'S'
and ap = 'A'
;
/*
update t_costimat0
set costomag = 0
where '$[val_acqcomm]' <> 'S'
and ap = 'A'
;
*/

delete from t_costimat
 where dtelab = '$[dtelab]' and comm = '$[commessa]'
;
insert into t_costimat
select '$[dtelab]' dtelab, *, 
/*
case when ksca >= kcar then kcar else 0 end kcomm,
case when ksca > kcar then (ksca-kcar) else 0 end kart,
*/
/*
case when kpre > kver then (kpre-kver) else 0 end kcomm,
0 kart,
*/
case when kpre > kver and kpre-kver >= kcar then kcar 
     when kpre > kver and kpre-kver < kcar then kpre-kver 
     else 0 end kcomm,
case when kpre > kver and kpre-kver >= kcar then kpre-kver-kcar
     when kpre > kver and kpre-kver < kcar then 0 
     else 0 end kart,
case when ksca < kcar then (kcar-ksca) else 0 end kgiac,
0,0,0
from t_costimat0
;
--Estrazione costo materiali per articolo
update t_costimat set
  val_comm = kcomm*nvl(acqcomm,0),
  val_art  = kart*nvl(costomag,0),
  val_giac = kgiac*nvl(case when ap = 'A' then acqcomm else costomag end,0)
 where dtelab = '$[dtelab]' and comm = '$[commessa]'
;
delete from t_costicom
 where dtelab = '$[dtelab]' and comm = '$[commessa]'
;
insert into t_costicom
select '$[dtelab]', comm, sotc, arti, null::char, voce, llcd, 'MAK' voce, 0, 0, kcomm, val_comm, kcomm
from t_costimat
where nvl(val_comm,0) <> 0
and dtelab = '$[dtelab]' and comm = '$[commessa]'
;
insert into t_costicom
select '$[dtelab]', comm, sotc, arti, null::char, voce, llcd, 'MAG' voce, 0, 0, kart, val_art, kart
from t_costimat
where nvl(val_art,0) <> 0
and dtelab = '$[dtelab]' and comm = '$[commessa]'
;
insert into t_costicom
select '$[dtelab]', comm, sotc, arti, null::char, voce, llcd, 'GIA' voce, 1, 0, kgiac, val_giac, kgiac
from t_costimat
where nvl(val_giac,0) <> 0
and dtelab = '$[dtelab]' and comm = '$[commessa]'
;
insert into t_costicom
select '$[dtelab]', nvl(ofc_comm,oft_comm), ofc_sotc, ofc_arti, null::char, nvl('$[vocetot]','$[vocegen]') voce, nvl(amg_llcd,0) llcd, 'ORF' voce, 2, 0, 
UVL(ofc_qord-ofc_qcon),
(UVL(ofc_qord-ofc_qcon)) * NVL(ofc_preu,0) * Zvl(ofc_qext) / Uvl(ofc_qord) * UVL(oft_cambe) / UVL(oft_camb) 
    * (100 - NVL(ofc_scon,0)) / 100 * (100 - NVL(ofc_sco2,0)) / 100 * (100 - NVL(ofc_sco3,0)) / 100,
UVL(ofc_qord-ofc_qcon)
from ofordic, ofordit, outer mganag
where ofc_tipo = oft_tipo and ofc_code = oft_code
and ofc_arti = amg_code
and nvl(ofc_comm,oft_comm) = '$[commessa]'
and ofc_feva = 'N'
;
delete from t_costiext
 where dtelab = '$[dtelab]' and comm = '$[commessa]'
;
;
--C/L di prodotto
insert into t_costiext
select '$[dtelab]', nvl(gim_comm,blt_comm) comm, gim_sotc sotc, gim_arti arti, 'CLP' tcl,
(select min(cce_voca) from mpcicli, mpcencos 
 where mci_parte = amg_code and mci_code = amg_crif and mci_tipo = 'E'
 and mci_centro = cce_cent and blt_data between nvl(cce_dain,mdy(1,1,1900)) and nvl(cce_dafi,mdy(12,31,2099))
 and cce_tcos = '$[tipocosto]') voce, 
amg_llcd llcd, nvl(gim_nord, blt_nord) nord, gim_rord rord, gim_data data, gim_code id, gim_riga riga, 
blt_cocl clifor, blt_fc-blt_fs cs, blt_tipo caus, gim_qmov qta, 
nvl(gim_prlav,gim_preu) gim_preu, nvl(gim_prlav,gim_preu)*gim_qmov imprig
from mggior, bfbolt, mgcausa, mganag, outer agclifor
where gim_code = blt_code 
and blt_tipo = cmg_code
and gim_arti = amg_code
and blt_cocl = cod_clifor
--and amg_tcom = 'P'
and gim_comm = '$[commessa]'
and gim_data <= '$[dtelab]'
--and nvl(amg_fagi ,'S') = 'S'
and cmg_casc = 'C' and nvl(cmg_fter,'N') <> 'N'
and gim_rord is not null 
and gim_depo = 1
;
--C/L di fase
insert into t_costiext
select '$[dtelab]', nvl(gim_comm,blt_comm) comm, gim_sotc,  gim_arti arti, 'CLF' tcl, 
(select cce_voca from mpcicli, mpcencos 
 where mci_parte = amg_code and mci_code = amg_crif and mci_tipo = 'E' and mci_sequ = gim_rord
 and mci_centro = cce_cent and blt_data between nvl(cce_dain,mdy(1,1,1900)) and nvl(cce_dafi,mdy(12,31,2099))
 and cce_tcos = '$[tipocosto]') voce, 
amg_llcd llcd, nvl(gim_nord, blt_nord) nord, gim_rord, gim_data, gim_code, gim_riga, 
blt_cocl clifor, blt_fc-blt_fs cs, blt_tipo, gim_qmov, 
nvl(gim_prlav,gim_preu) gim_preu, nvl(gim_prlav,gim_preu)*gim_qmov imprig
from mggior, bfbolt, mgcausa, mganag, outer agclifor
where gim_code = blt_code 
and blt_tipo = cmg_code
and gim_arti = amg_code
and blt_cocl = cod_clifor
--and amg_tcom = 'P'
and gim_comm = '$[commessa]'
and gim_data <= '$[dtelab]'
--and nvl(amg_fagi ,'S') = 'S'
and cmg_casc = 'C' and nvl(cmg_fter,'N') <> 'N'
and gim_rord is null 
and gim_depo = 1
;
--Estrazione costo terzisti per articolo
insert into t_costicom
select '$[dtelab]', comm, sotc, arti, clifor, nvl(voce,'$[voceext]'), llcd, tcl /*'EXT'*/, 0, 0, 
sum(qta), sum(imprig), sum(qta)
from t_costiext
where dtelab = '$[dtelab]' and comm = '$[commessa]'
group by 1,2,3,4,5,6,7,8
having sum(imprig) <> 0
;
insert into t_costicom
select '$[dtelab]', gim_comm, gim_sotc, gim_arti, null::char, '$[vocegen]', 99, 'GEN', 0, 0, 
sum(gim_qmov), sum(gim_qmov*(blt_fc-blt_fs)*gim_preu) imprig, sum(gim_qmov)
from mggior, bfbolt, mgcausa
where gim_code = blt_code 
and blt_tipo = cmg_code
--and (gim_arti is null or gim_arti in (select amg_code from mganag where nvl(amg_fagi ,'S') = 'N'))
and gim_arti is null
and gim_comm = '$[commessa]'
and gim_data <= '$[dtelab]'
group by 1,2,3,4,5
having sum(gim_qmov*(blt_fc-blt_fs)*gim_preu) <> 0
;
--Estrazione fatture passive (no articolo o no a giacenza)
/*
wbexport -type=xlsx -file=$[path]\$[filename].xlsx -title=fatture_passive -fixedDigits=2 -append=true
;
select ffc_sotc sotto_commessa, trim(fft_regi)||'/'||fft_anno||'/'||fft_prot documento, ffc_arti arti, amg_dest descrizione,
 sum(ffc_qext) qta, sum(sgn*ffc_qext*ffc_preu*(100-ffc_scon)/100*(100-ffc_sco2)/100*(100-ffc_sco3)/100) importo
from
(   select ffc_comm, ffc_sotc, ffc_sotc, fft_regi,fft_anno,fft_numf,fft_cofo,ffc_arti,nvl(amg_fagi,'s') fagi, 
	  nvl(ffc_des1,amg_dest) amg_dest,
	  ffc_nord,ffc_tord,ffc_rord,ffc_nbol,ffc_rbol,
	  uvl(ffc_qext) ffc_qext,zvl(ffc_preu) ffc_preu,zvl(ffc_scon) ffc_scon,zvl(ffc_sco2) ffc_sco2,
	  zvl(ffc_sco3) ffc_sco3,fft_datf,fft_valu,uvl(fft_camb),uvl(fft_cambe), 
	  fft_prot, case when fft_tmov = 'N' then -1 else 1 end sgn
     from mgfafoc, mgfafot,outer mganag
     where ffc_comm = '$[commessa]'
	and fft_dtreg <= '$[dtelab]'
        and ffc_code = fft_code
        and ffc_arti = amg_code
)
where ffc_arti is null or fagi = 'N'
group by 1,2,3,4
order by 1,2,3
;
*/
insert into t_costicom
select '$[dtelab]', ffc_comm, ffc_sotc, ffc_arti, null::char, '$[voceffp]', 99, 'FFP', 0, 0, 
sum(sgn*ffc_qext), sum(sgn*ffc_qext*ffc_preu*(100-ffc_scon)/100*(100-ffc_sco2)/100*(100-ffc_sco3)/100) imprig, sum(sgn*ffc_qext)
from
(   select ffc_comm, ffc_sotc, fft_regi,fft_anno,fft_numf,fft_cofo,ffc_arti,nvl(amg_fagi,'s') fagi,ffc_des1,ffc_umif,
	  ffc_nord,ffc_tord,ffc_rord,ffc_nbol,ffc_rbol,
	  uvl(ffc_qext) ffc_qext,zvl(ffc_preu) ffc_preu,zvl(ffc_scon) ffc_scon,zvl(ffc_sco2) ffc_sco2,
	  zvl(ffc_sco3) ffc_sco3,fft_datf,fft_valu,uvl(fft_camb),uvl(fft_cambe), 
	  fft_prot, case when fft_tmov = 'N' then -1 else 1 end sgn
     from mgfafoc, mgfafot,outer mganag
     where ffc_comm = '$[commessa]'
	and fft_dtreg <= '$[dtelab]'
        and ffc_code = fft_code
        and ffc_arti = amg_code
	and ffc_nbol is null
)
where ffc_arti is null or fagi = 'N'
group by 1,2,3,4
;
delete from t_tempilav
 where dtelab = '$[dtelab]' and comm = '$[commessa]'
;
--Tempi setup
insert into t_tempilav
select '$[dtelab]', mol_comm comm, mol_sotc sotc, mvt_ordl odl, mvt_data data, 'HST' tipo, mol_parte arti, amg_llcd llcd, mvt_cent cdl, 
mol_quari qta, (mvt_etah+mvt_etam/$[frazione_tempi])/$[base_tempi] hh, mce_cset tariffa
from mpordil, mptemfas, mganag, mpcentri
where mol_code = mvt_ordl
and mvt_cent = mce_code
and mol_parte = amg_code
and mol_comm = '$[commessa]'
and mvt_data <= '$[dtelab]'
and (mvt_etah > 0 or mvt_etam > 0)
;
--Tempi manodopera
insert into t_tempilav
select '$[dtelab]', mol_comm comm, mol_sotc, mvt_ordl, mvt_data, 'HMO' tipo, mol_parte arti, amg_llcd llcd, mvt_cent cdl, 
mol_quari, (mvt_etuh+mvt_etum/$[frazione_tempi])/$[base_tempi] hh, mce_cman tariffa
from mpordil, mptemfas, mganag, mpcentri
where mol_code = mvt_ordl
and mvt_cent = mce_code
and mol_parte = amg_code
and mol_comm = '$[commessa]'
and mvt_data <= '$[dtelab]'
and (mvt_etuh > 0 or mvt_etum > 0)
;
--Tempi lavorazione
insert into t_tempilav
select '$[dtelab]', mol_comm comm, mol_sotc, mvt_ordl, mvt_data, 'HMC' tipo, mol_parte arti, amg_llcd llcd, mvt_cent cdl, 
mol_quari, (mvt_etmh+mvt_etmm/$[frazione_tempi])/$[base_tempi] hh, mce_cmac tariffa
from mpordil, mptemfas, mganag, mpcentri
where mol_code = mvt_ordl
and mvt_cent = mce_code
and mol_parte = amg_code
and mol_comm = '$[commessa]'
and mvt_data <= '$[dtelab]'
and mvt_etmh > 0
;
--Tempi generici su ODL (mptempi)
insert into t_tempilav
select '$[dtelab]', mtf_comm, mtf_sotc, mtf_ordl, mtf_data, 'HOL', null::char, 99, mtf_cent cdl, 
0, (mtf_hhto+mtf_mmto/$[frazione_tempi])/$[base_tempi] hh, '$[tariffahhodl]' tariffa
from mptempi, mpcentri
where mtf_cent = mce_code 
and mtf_comm = '$[commessa]'
and mtf_data <= '$[dtelab]'
and mtf_ordl is not null
and (mtf_hhto > 0 or mtf_mmto > 0)
;
--Tempi generici su commessa (mptempi)
insert into t_tempilav
select '$[dtelab]',  mtf_comm, mtf_sotc, null::integer, mtf_data, 'HGE', null::char, 99, mtf_cent cdl, 
0, (mtf_hhto+mtf_mmto/$[frazione_tempi])/$[base_tempi], '$[tariffahhgen]' tariffa
from mptempi, mpcentri
where mtf_cent = mce_code
and mtf_comm = '$[commessa]'
and mtf_data <= '$[dtelab]'
and mtf_ordl is null
and (mtf_hhto > 0 or mtf_mmto > 0)
;
insert into t_costicom
select '$[dtelab]', comm, sotc, arti, cdl, cce_voca, llcd, tipo, 0, 0 liv,
--(select sum(mol_quari) from mpordil where mol_comm = '$[commessa]'), sum(hh*cce_cooa)
sum(hh), sum(hh*cce_cooa), (select sum(mol_quari) from mpordil where mol_code = odl)
from t_tempilav, mpcencos
where cdl = cce_cent
and dtelab = '$[dtelab]' and comm = '$[commessa]'
and data between nvl(cce_dain,mdy(1,1,1900)) and nvl(cce_dafi,mdy(12,31,2099))
and cce_tcos = '$[tipocostohh]'
and tariffa like '%A%'
group by 1,2,3,4,5,6,7,8,9,10,13
having sum(hh*cce_cooa) <> 0
;
insert into t_costicom
select '$[dtelab]', comm, sotc, arti, cdl, cce_voca, llcd, tipo, 0, 0 liv,
--(select sum(mol_quari) from mpordil where mol_comm = '$[commessa]'), sum(hh*cce_coob)
sum(hh), sum(hh*cce_coob), (select sum(mol_quari) from mpordil where mol_code = odl)
from t_tempilav, mpcencos
where cdl = cce_cent
and dtelab = '$[dtelab]' and comm = '$[commessa]'
and data between nvl(cce_dain,mdy(1,1,1900)) and nvl(cce_dafi,mdy(12,31,2099))
and cce_tcos = '$[tipocostohh]'
and tariffa like '%B%'
group by 1,2,3,4,5,6,7,8,9,10,13
having sum(hh*cce_coob) <> 0
;
insert into t_costicom
select '$[dtelab]', comm, sotc, arti, cdl, cce_voca, llcd, tipo, 0, 0 liv,
--(select sum(mol_quari) from mpordil where mol_comm = '$[commessa]'), sum(hh*cce_cooc)
sum(hh), sum(hh*cce_cooc), (select sum(mol_quari) from mpordil where mol_code = odl)
from t_tempilav, mpcencos
where cdl = cce_cent
and dtelab = '$[dtelab]' and comm = '$[commessa]'
and data between nvl(cce_dain,mdy(1,1,1900)) and nvl(cce_dafi,mdy(12,31,2099))
and cce_tcos = '$[tipocostohh]'
and tariffa like '%C%'
group by 1,2,3,4,5,6,7,8,9,10,13
having sum(hh*cce_cooc) <> 0
;
insert into t_costicom
select '$[dtelab]', comm, sotc, arti, cdl, cce_voca, llcd, tipo, 0, 0 liv,
--(select sum(mol_quari) from mpordil where mol_comm = '$[commessa]'), sum(hh*cce_cood)
sum(hh), sum(hh*cce_cood), (select sum(mol_quari) from mpordil where mol_code = odl)
from t_tempilav, mpcencos
where cdl = cce_cent
and dtelab = '$[dtelab]' and comm = '$[commessa]'
and data between nvl(cce_dain,mdy(1,1,1900)) and nvl(cce_dafi,mdy(12,31,2099))
and cce_tcos = '$[tipocostohh]'
and tariffa like '%D%'
group by 1,2,3,4,5,6,7,8,9,10,13
having sum(hh*cce_cood) <> 0
;
insert into t_costicom
select '$[dtelab]', comm, sotc, arti, cdl, cce_voca, llcd, tipo, 0, 0 liv,
--(select sum(mol_quari) from mpordil where mol_comm = '$[commessa]'), sum(hh*cce_cooe)
sum(hh), sum(hh*cce_cooe), (select sum(mol_quari) from mpordil where mol_code = odl)
from t_tempilav, mpcencos
where cdl = cce_cent
and dtelab = '$[dtelab]' and comm = '$[commessa]'
and data between nvl(cce_dain,mdy(1,1,1900)) and nvl(cce_dafi,mdy(12,31,2099))
and cce_tcos = '$[tipocostohh]'
and tariffa like '%E%'
group by 1,2,3,4,5,6,7,8,9,10,13
having sum(hh*cce_cooe) <> 0
;
insert into t_costicom
select '$[dtelab]', comm, sotc, arti, cdl, cce_voca, llcd, tipo, 0, 0 liv,
--(select sum(mol_quari) from mpordil where mol_comm = '$[commessa]'), sum(hh*cce_coof)
sum(hh), sum(hh*cce_coof), (select sum(mol_quari) from mpordil where mol_code = odl)
from t_tempilav, mpcencos
where cdl = cce_cent
and dtelab = '$[dtelab]' and comm = '$[commessa]'
and data between nvl(cce_dain,mdy(1,1,1900)) and nvl(cce_dafi,mdy(12,31,2099))
and cce_tcos = '$[tipocostohh]'
and tariffa like '%F%'
group by 1,2,3,4,5,6,7,8,9,10,13
having sum(hh*cce_coof) <> 0
;
insert into t_costicom
select '$[dtelab]', comm, sotc, arti, cdl, cce_voca, llcd, tipo, 0, 0 liv,
--(select sum(mol_quari) from mpordil where mol_comm = '$[commessa]'), sum(hh*cce_coog)
sum(hh), sum(hh*cce_coog), (select sum(mol_quari) from mpordil where mol_code = odl)
from t_tempilav, mpcencos
where cdl = cce_cent
and dtelab = '$[dtelab]' and comm = '$[commessa]'
and data between nvl(cce_dain,mdy(1,1,1900)) and nvl(cce_dafi,mdy(12,31,2099))
and cce_tcos = '$[tipocostohh]'
and tariffa like '%G%'
group by 1,2,3,4,5,6,7,8,9,10,13
having sum(hh*cce_coog) <> 0
;
insert into t_costicom
select '$[dtelab]', comm, sotc, arti, cdl, cce_voca, llcd, tipo, 0, 0 liv,
--(select sum(mol_quari) from mpordil where mol_comm = '$[commessa]'), sum(hh*cce_cooh)
sum(hh), sum(hh*cce_cooh), (select sum(mol_quari) from mpordil where mol_code = odl)
from t_tempilav, mpcencos
where cdl = cce_cent
and dtelab = '$[dtelab]' and comm = '$[commessa]'
and data between nvl(cce_dain,mdy(1,1,1900)) and nvl(cce_dafi,mdy(12,31,2099))
and cce_tcos = '$[tipocostohh]'
and tariffa like '%H%'
group by 1,2,3,4,5,6,7,8,9,10,13
having sum(hh*cce_cooh) <> 0
;
insert into t_costicom
select '$[dtelab]', comm, sotc, arti, cdl, cce_voca, llcd, tipo, 0, 0 liv,
--(select sum(mol_quari) from mpordil where mol_comm = '$[commessa]'), sum(hh*cce_cooi)
sum(hh), sum(hh*cce_cooi), (select sum(mol_quari) from mpordil where mol_code = odl)
from t_tempilav, mpcencos
where cdl = cce_cent
and dtelab = '$[dtelab]' and comm = '$[commessa]'
and data between nvl(cce_dain,mdy(1,1,1900)) and nvl(cce_dafi,mdy(12,31,2099))
and cce_tcos = '$[tipocostohh]'
and tariffa like '%I%'
group by 1,2,3,4,5,6,7,8,9,10,13
having sum(hh*cce_cooi) <> 0
;
insert into t_costicom
select '$[dtelab]', comm, sotc, arti, cdl, cce_voca, llcd, tipo, 0, 0 liv, 
--(select sum(mol_quari) from mpordil where mol_comm = '$[commessa]'), sum(hh*cce_cooj)
sum(hh), sum(hh*cce_cooj), (select sum(mol_quari) from mpordil where mol_code = odl)
from t_tempilav, mpordil, mpcencos
where cdl = cce_cent
and dtelab = '$[dtelab]' and comm = '$[commessa]'
and odl = mol_code
and data between nvl(cce_dain,mdy(1,1,1900)) and nvl(cce_dafi,mdy(12,31,2099))
and cce_tcos = '$[tipocostohh]'
and tariffa like '%J%'
group by 1,2,3,4,5,6,7,8,9,10,13
having sum(hh*cce_cooj) <> 0
;
/*
--Estrazione costi per articolo-voce
wbexport -type=xlsx -file=$[path]\$[filename].xlsx -title=costo_articoli_voce -fixedDigits=2 -append=true
;
select comm, sotc, nvl(arti,'<ND>') arti, nvl(cdl,'<ND>') cdl, voce, llcd, cls, tpcls, liv, costo 
from t_costicom
order by 1,2,3,4
;
*/
drop table tmp_prellivinf
;
select nvl(gim_comm,blt_comm) comm, gim_sotc sotc, null::char(1) tord, nvl(gim_nord, blt_nord) nord, null::int rord, mol_parte padre, blt_fc-blt_fs cs, 
gim_arti arti, amg_llcd llcd, blt_tipo caus, gim_qmov qta, 0::dec(14,5) costo_li
from mggior, bfbolt, mgcausa, mganag, mpordil
where gim_code = blt_code 
and blt_tipo = cmg_code
--and gim_arti = amg_code
and mol_parte = amg_code
and mol_code = nvl(gim_nord, blt_nord)
--and amg_tcom = 'P'
and gim_comm = '$[commessa]'
and gim_data <= '$[dtelab]'
and gim_nord is not null
and gim_tord is null
--and nvl(amg_fagi ,'S') = 'S'
union all
select nvl(gim_comm,blt_comm) comm, gim_sotc sotc, ofc_tipo, ofc_code, ofc_riga, ofc_arti padre, blt_fc-blt_fs cs, 
gim_arti arti, amg_llcd llcd, blt_tipo caus, gim_qmov qta, 0::dec(14,5) costo_li
from mggior, bfbolt, mgcausa, mganag, mpfabbi, ofordic
where gim_code = blt_code 
and blt_tipo = cmg_code
--and gim_arti = amg_code
and gim_rord = mpf_key
and mpf_tiof = ofc_tipo and mpf_coof = ofc_code and mpf_riof = ofc_riga
and ofc_arti = amg_code
--and amg_tcom = 'P'
and gim_comm = '$[commessa]'
and gim_data <= '$[dtelab]'
and gim_nord is not null
and gim_tord is not null
--and nvl(amg_fagi ,'S') = 'S'
into temp tmp_prellivinf
;
/*
--Estrazione prelievi da livelli inferiori
wbexport -type=xlsx -file=$[path]\$[filename].xlsx -title=prelievi_livinf -fixedDigits=2 -append=true
;
select * from tmp_prellivinf
;
*/
--loop-i
/*
wbrunresult
;
select distinct --llcd 
 "insert into t_costicom" ||
 " select '$[dtelab]', '$[commessa]', p.sotc, p.padre, c.cdl, c.voce, p.llcd, c.cls, c.tpcls, 1, sum(p.qta), sum(p.qta*c.costo/uvl(c.qtaxli)), sum(p.qta)" ||
 " from tmp_prellivinf p, t_costicom c" ||
 " where p.arti = c.arti" ||
 " and c.comm = '" || '$[commessa]' || "'" ||
 " and c.dtelab = '" || '$[dtelab]' || "'" ||
 " and p.llcd = " || llcd ||
 " group by 1,2,3,4,5,6,7,8,9; "
from tmp_prellivinf
 where llcd > 0
 order by 1 desc
;
*/
--loop-f