/*
In ambito gestionale una delle questioni fondamentali da affrontare in ambito statistico è
quella della valorizzazione del magazzino.
Affrontando questa questione è facile rendersi conto che il concetto di valorizzazione non
è univoco, consideriamo infatti subito il seguente esempio:

In magazzino abbiamo un prodotto X che abbiamo acquistato a 10€ e che intendiamo
rivendere a 15€. Abbiamo già due prezzi e quindi due valorizzazioni differenti, una al prezzo
di acquisto e una al prezzo di vendita.
Se le questioni finissero qui sarebbe comunque abbastanza semplice elaborare entrambe le
valorizzazioni e lasciare che l'utente le possa valutare entrambe, ma, naturalmente, le
questioni non sono finite qui.
Consideriamo ancora un esempio, un semplice negozio:

- A gennaio il negozio acquista un prodotto X a 7€
- A febbraio il negozio acquista un prodotto dello stesso tipo X a 8€
- A marzo il negozio vende un prodotto, sembre di tipo X, a 12€

a fine anno avremo quindi un prodotto X residuo, ma quale dei due? Sorgono sponaee alcune
scelte possibili, per esempio:

- Il prodotto restante è quello a 7€
- Il prodotto restante è quello a 8€
- Il prodotto restante ha un valore medio di 7.5€

La prima scelta può nascere da un principio generale del tipo "Si vende prima la merce acquistata
per ultima", scelta denominata usualmente Lifo [acronimo inglese di Last In First Out], la seconda
da un principio opposto del tipo "Si vende prima la merce acquistata per prima", e questa è detta
Fifo [acronimo inglese di First In First Out], l'ultima valorizzazione si basa invece sul calcolo di un
prezzo medio.
Nel seguito parleremo più brevemente di Calcolo del Fifo, del Lifo o della valorizzazione media
ponderata.

In ogni caso tutti e tre i tipi di valorizzazione richiedono un'analisi della totalità dei movimenti e,
normalmente l'ausilio di una gran quantità di risorse.
Si cercherà qui di affrontare questi temi con query TSQL inline, senza quindi far uso di cursori nè
l'ausilio del CLR.
Ignoreremo per il momento il calcolo del valore di magazzino medio ponderato e ci concentreremo
su Lifo e Fifo.
Il problema è per sua natura row based, quindi ci sarà da mettersi le mani nei capelli, vediamo il
perchè di tale affermazione. Consideriamo un solo prodotto X, movimentato varie volte durante
l'anno, useremo valori interi con segno per indicare le quntità caricate o scaricate e un progressivo,
per semplicità, al posto della data, per esempio:

01) + 4, 3€
02) + 4, 4€
03) - 6, 10€

indica che:

Sono stati caricati 4 pezzi di x a 3€ cadauno, dopo ne sono stati caricati altri 4 a 4€ e infine ne sono
stati scaricati [Presumibilmente venduti] 6 a 10€ cadauno.
Supponiamo ora di voler calcolare il Lifo, quindi si suppone che siano stati venduti i prodotti
acquistati per ultimi. 
Per inciso, questa opzione, che sembra la meno logica, è una delle preferite, in questo modo infatti
il magazzino ha in generale un valore più basso, essendo rimossi gli acquisti a prezzo
presumibilmente maggiore.
Comunque, nel nostro caso le sei vendite, nel calcolo Lifo, derivano dall'alienazione di tutti e 4 i pezzi
a 4€ e due dei pezzi a 3€.
Il valore della merce residua, calcolato tramite Lifo, è quindi 6€.
Come scrivere un algoritmo che faccia quelsto calcolo?
Una possibilità si basa su queto approccio. Analizzaimo i dati al contrario dal più recente al più vecchio
e, riga per riga, riportiamo il totale, nel nostro caso:

03) - 6, 10€ --> -6
02) + 4, 4€ --> -6+4 = -2
01) + 4, 3€ --> -2+4 = 2

Le righe con valore positivo sono quelle da valorizzare. Consideriamo però un caso leggermente più
complesso:

01) + 4, 3€
02) + 4, 4€
03) - 6, 10€
04) + 2, 5€
05) - 1, 10€

La vendita in 05 preleva merce caricata 04, mentre la vendita in 03 si comporta esattamente come
il caso precendente, quindi, il residuo è di un pezzo a 5€ e 2 pezzi a 4€, ma se iniziamo ad applicare
il nostro algoritmo otteniamo:

05) - 1, 10€ --> -1
04) + 2, 5€ --> -1+2 = 1
03) - 6, 10€ --> +1-6 = 5 No!

La quantità positiva +1 ottenuta in 04 non ha più alcun effetto sui movimenti precedenti e va quindi
azzerata, si tratta quindi non di una semplice somma, ma di una somma "massimizzata a 0",
correttamente sarà:

05) - 1, 10€ --> -1
04) + 2, 5€  --> -1+2 = 1
03) - 6, 10€ --> -6 Ignoro il precedente valore perchè positivo!
02) + 4, 4€ --> -6+4 = -2 
01) + 4, 3€ --> -2+4 = +2

L'agoritmo corretto, ha quindi questa forma:

QuantitàLifo(i) = Quantità(i) + (QuantitàLifo(i-1) > 0 ? 0 : QuantitàLifo(i-1))

La definizione è quindi ricorsiva, o, detto in altro modo, progressiva, nel senso che va valutata riga
per riga e richiede uno solo scan completo per arrivare alla fine.
Introduciamo ora un po' di tsql e creiamo una tabella di movimenti:
*/

use tempdb
set nocount on
go
create table t_Movimenti(Id_Movimento uniqueidentifier primary key, 
    /*Aggiungiamo anche un ID Articolo anche se, per ora, ne considereremo uno solo*/
    ID_Articolo uniqueidentifier not null,
    Data datetime not null,
    Quantità int not null,
    ValoreUnitario money not null,
    -- Aggiungo un check per evitare movimenti non significativi
    check(Quantità!=0))
go

/*
Mettiamo ora  in questa tabella i dati del nostro esempio "avanzato":
*/
declare @ID_Articolo uniqueidentifier
set @ID_Articolo=newid()

insert into t_Movimenti values(newid(), @ID_Articolo, getdate(), 4, 3)
insert into t_Movimenti values(newid(), @ID_Articolo, getdate() +1, 4, 4)
insert into t_Movimenti values(newid(), @ID_Articolo, getdate() +2, -6, 10)
insert into t_Movimenti values(newid(), @ID_Articolo, getdate() +3, 2, 5)
insert into t_Movimenti values(newid(), @ID_Articolo, getdate() +4, -1, 10)
-- select * from t_Movimenti order by Data
go

/*
Il calcolo del running total massimizzato è una questione rognosa. Itzik Ben-Gan ha rilasciato a
proposito di questa e analoghe questioni un documento di richieste di completamento delle potenzialità
di sql che, per chi fosse interessato, può essere scaricato qui.
In questo caso useremo la strategia descritta in questo post per eseguire calcoli sequenziali.
Creiamo anzitutto la funzione che estrae il record successivo [dalla data più recente all'indietro, come
da algoritmo]. Oltre alla data sfrutteremo anche anche l'id movimento per dirimere i casi di identitià di
data [che non è chiave]:
*/
create function f_LifoNext(@Data datetime, @ID_Movimento uniqueidentifier)
returns table as return
    select top 1 * from dbo.t_Movimenti 
    where Data<@Data 
    or Data=@Data and ID_Movimento<@ID_Movimento
    order by Data desc
go
/*
Passiamo ora alla cte ricorsiva per l'estrazione row by row
*/
create function f_LifoResidui()
returns table as return

with lf as(
    select top 1 *,
        /*Il Valore iniziale di running total è, banalmente, la Quantità*/
        Q = Quantità
    from t_Movimenti 
    order by Data desc, ID_Movimento desc

    union all

    select n.*,
        /*Il Valore corrente di running total è la Quantità a cui aggiungere un eventuale valore precedente passivo*/
        Q = n.Quantità + case when lf.Q > 0 then 0 else lf.Q end
    from lf
    cross apply dbo.f_LifoNext(lf.Data, lf.ID_Movimento) n
)
    select ID_Movimento, Data, ValoreUnitario, Q
    from lf
    /*Infine consideriamo solo i valori positivi*/
    where Q > 0
go
-- select * from f_LifoResidui()

/*
La valorizzazione del nostro magazzino si ridurrà ora a una sommatoria sui residui:
*/
create function f_LifoArticolo()
returns table as return
    select sum(Q) Quantità, sum(Q*ValoreUnitario) Valore
    from f_LifoResidui()
go
-- select * from dbo.f_LifoArticolo()

/*
Il nome dato alla funzione f_LifoArticolo non è casuale. In realtà noi abbiamo finora considerato un
unico articolo ed abbiamo calcolato il lifo sulla base di questa supposizione. In realtà tuttavia un vero
gestionale contiene molti articoli ed andremo ora ad ampliare l'esempio con qualche nuova aggiunta.
Aggiungiamo anzitutto un nuovo articolo con una movimentazione simile al primo ma quantità 10 
volte superiori:
*/

declare @ID_Articolo uniqueidentifier
set @ID_Articolo=newid()

insert into t_Movimenti values(newid(), @ID_Articolo, getdate(), 40, 3)
insert into t_Movimenti values(newid(), @ID_Articolo, getdate() +1, 40, 4)
insert into t_Movimenti values(newid(), @ID_Articolo, getdate() +2, -60, 10)
insert into t_Movimenti values(newid(), @ID_Articolo, getdate() +3, 20, 5)
insert into t_Movimenti values(newid(), @ID_Articolo, getdate() +4, -10, 10)
go

/*Ed inseriamo anche un articolo con movimentazione incorente. Per esempio un articolo venduto
senza essere mai stato caricato. 
Si tratta di una situazione impossibile nella realtà ma possibilissima nella modellazione che un
gestionale fa della realtà:*/
declare @ID_Articolo uniqueidentifier
set @ID_Articolo=newid()
insert into t_Movimenti values(newid(), @ID_Articolo, getdate(), -10, 5)

go

/*Ancora, inseriamo un articolo con una prima movimentazione incorente e poi un acquisto venduto
correttamente:*/
declare @ID_Articolo uniqueidentifier
set @ID_Articolo=newid()
insert into t_Movimenti values(newid(), @ID_Articolo, getdate(), -10, 5)
insert into t_Movimenti values(newid(), @ID_Articolo, getdate()+1, 7, 5)
insert into t_Movimenti values(newid(), @ID_Articolo, getdate()+2, -6, 5)
go

/*
Per poter lavorare su più articoli manteniamo l'identica struttura precedente, specificando però per
ogni funzione l'articolo su cui si sta lavorando in quel momento. Partiamo dalla f_LifoNext aggiungendo
un parametro e una clausola di where:
*/

alter function f_LifoNext(@Data datetime, @ID_Movimento uniqueidentifier, @ID_Articolo uniqueidentifier)
returns table as return
    select top 1 * from dbo.t_Movimenti 
    where ID_Articolo=@ID_Articolo 
    and (Data<@Data 
        or Data=@Data and ID_Movimento<@ID_Movimento)
    order by Data desc
go

/*
Quindi modifichiamo di conseguenza la f_LifoResidui
*/

alter function f_LifoResidui(@ID_Articolo uniqueidentifier)
returns table as return

with lf as(
    select top 1 *,
        /*Il Valore iniziale di running total è, banalmente, la Quantità*/
        Q = Quantità
    from t_Movimenti 
    where ID_Articolo = @ID_Articolo
    order by Data desc, ID_Movimento desc

    union all

    select n.*,
        /*Il Valore corrente di running total è la Quantità a cui aggiungere un eventuale valore precedente passivo*/
        Q = n.Quantità + case when lf.Q > 0 then 0 else lf.Q end
    from lf
    cross apply dbo.f_LifoNext(lf.Data, lf.ID_Movimento, @ID_Articolo) n
)
    select ID_Movimento, Data, ValoreUnitario, Q
    from lf
    /*Infine consideriamo solo i valori positivi*/
    where Q > 0
go

/*
Ed infine la f_LifoResidui
*/

alter function f_LifoArticolo(@ID_Articolo uniqueidentifier)
returns table as return
    select sum(Q) Quantità, sum(Q*ValoreUnitario) Valore
    from f_LifoResidui(@ID_Articolo)
go

/* Per ottenere il risultato dobbiamo ora anzitutto trovare gli articoli movimentati e, per ognuno di essi, calcolare il lifo:*/

create function f_Lifo()
returns table as return

with a as(
    select distinct ID_Articolo from dbo.t_Movimenti
)
    select * from a
    cross apply dbo.f_LifoArticolo(a.ID_Articolo) l
go
-- select * from dbo.f_Lifo()
/*
 I dati ritornati sono corretti, l'articolo movimentato in modo assurdo ha valori nulli e l'articolo
movimentato in modo parzialmente assurdo ha valori corretti.
Certo che il piano d'esecuzione non è dei migliori. Per ottenere buone performances dobbiamo
garantire un'accesso velocissimo al blocco di movimenti relaitivi ad un certo articolo e, all'interno
di ogni blocco un accesso velocissimo alla data in modo da ottimizzare al massimo la f_LifoNext
che è sicuramente il collo di bottiglia di tutto il calcolo.
Completeremo l'indice con Quantità e ValoreUnitario per evitare fastidiosi bookmark lookaps, 
quindi:
*/

create index i1 on t_Movimenti(ID_Articolo, Data) include(Quantità,ValoreUnitario)
select * from dbo.f_Lifo()
go

/*
Il valore complessivo di magazzino sarà dato da un banale:
*/

select Sum(Quantità) Quantità, 
    Sum(Valore) Valore 
from dbo.f_Lifo()

/*
Passiamo ora al calcolo del Fifo. In questo secondo tipo di calcolo le cose vanno al contrario, si presuppone
infatti di avere venduto prima la merce acquistata prima. Di conseguenza e poichè in prezi
tendenzialmente crescono anzichè calare, il Fifo tende a dare risultati più alti del Lifo.
Il fatto però che il Lifo sia concettualmente il contrario del Fifo non si traduce nel fatto che un semplice
cambio di segno o di ordinamento sia sufficiente a passare dal calcolo dell'uno all'altro, tra un attimo
vedremo perchè.
Riprendiamo il nostro esempio e valoriziamolo manualmente secondo le regole del Fifo:

01) + 4, 3€
02) + 4, 4€
03) - 6, 10€
04) + 2, 5€
05) - 1, 10€

In 3) avviene una vendita che va resa più remota possibile. Quindi si considereranno venduti gli articoli caricati
in 01 e due dei quattro caricati in 02. La vendita in 05 avverrà su quest'ultimo residuo, quindi il Fifo elabora, 
partendo da questi dati, una rimanenza di un solo articolo a 4€ e 2 a 5€ per un valore complessivo di 14€ 
[ricordiasmoci che il calcolo del Lifo dava invece 11€, due pezzi a 3€ e 1 a 5€]
Già in questo semplicissimo esempio è possibile capire perchè il calcolo del Fifo è sostanzialmente diverso dal
calcolo del Lifo.
Cerchiamo di capire lo stato di reduo in 02 secondo i due medoti:

Nel caso del Lifo ci sono 7 vendite complessive nel futuro di 02, una delle quali è assorbita da 04, quindi in 02
restano da distribuire 6 scarichi e quindi il carico di 4 pezzi è completamente alientao. Si osservi come in
questo caso lo stato di residuo di 02 dipenda esclusivamente dal suo futuro.

Nel caso del Fifo ci sono ancora 7 vendite complessive nel futuro di 02 ma, avendo priorità il "first in", queste
vanno distribuite anzitutto su 01, nel passato di 02, e quindi lo stato di 02 dipende sia dal proprio passato
che dal proprio futuro!

Il significato di questa osservazione è che non potremo in alcun modo calcolare il Fifo con procedimento
esclusivamente progressivo come abbiamo fatto con il Lifo.
Se però conoscessimo il totale della vendite [Che richiederà un  index scan] potremmo forse ottenere
il risultato muovendoci in un'unica direzione [e quindi in totale due scansioni].

Calcoliamo anzitutto il totale delle vendite.
Totale scarichi= -7

Ora, procedendo dal passato al presente, le distribuiamo sui carichi. Per far ciò porteremo con noi,
calcolandole progressivamente ben 3 variabili, che hanno questo significato:

X è la quantità usata finora di scarichi. Si tratta di una variabile non strettamente necessaria per i casi
standard, ma che entra in gioco nel caso di dati incoerenti.
Consideriamo questo caso incoerente:

1) +2
2) -3
3) +1

Il totale degli scarichi è -3 ma lo scarico in 02 andrà corretto poichè incoerente. Tale correzione sarà
possibile nel momento in cui ci si accorgerà che i carichi accumulati arrivando a 02 [+2] sono minori
degli scarichi disponibili a quella data [-3].

D è la disponibilità di scarichi. Rappresenta la quantità corrente di scarichi che dobbiamo distribuire.

R è il resido di carico. Varrà 0 per gli scarichi e rappresenterà i movimenti utili per il calcolo, come
nel caso precedente.

Nel caso del Fifo, per semplicità, inccluderemo il calcolo delle variabili dentro la funzione Next:
*/
go
create function f_FifoNext(@ID_Articolo uniqueidentifier, @Id_Movimento uniqueidentifier, @Data datetime,  @D int, @X int)
returns table as return
    select top 1 *,
        R = case when @D<Quantità then Quantità-@D else 0 end,
        D = case when Quantità < 0 then 
                @D + case when @X + Quantità < 0 then @X + Quantità else 0 end
            else
                case when @D<Quantità then 0 else @D-Quantità end
            end,
        X = case when Quantità < 0 then 
                case when @X + Quantità < 0 then 0 else @X + Quantità end
            else
                @X + case when @D<Quantità then @D else Quantità end
            end
    from dbo.t_Movimenti 
    where ID_Articolo=@ID_Articolo
    and (Data>@Data 
        or Data=@Data and Id_Movimento>@Id_Movimento
        or @Data is null)
    order by Data, Id_Movimento
go
/* Creaiamo ora la Cte ricorsiva*/
create function f_FifoResidui(@ID_Articolo uniqueidentifier, @D int)
returns table as return

with r as(
    select * from dbo.f_FifoNext(@ID_Articolo, null, null, @D, 0) n

    union all

    select n.* from r
    cross apply dbo.f_FifoNext(@ID_Articolo, r.Id_Movimento, r.Data, r.D, r.X) n
)
    select * from r
go
/* Passiamo ora al calcolo di fifo per articolo*/
create function f_FifoArticolo(@ID_Articolo uniqueidentifier, @D int)
returns table as return
    select sum(R) Quantità, sum(R*ValoreUnitario) Valore
    from f_FifoResidui(@ID_Articolo, @D)
go
/*E infine al calcolo complessivo*/
create function f_Fifo()
returns table as return

with a as(
    select ID_Articolo, 
        D = sum(case when Quantità<0 then -Quantità else 0 end)
    from dbo.t_Movimenti
    group by ID_Articolo
)
    select a.ID_Articolo, l.* 
    from a
    cross apply dbo.f_FifoArticolo(a.ID_Articolo, a.D) l
go
/*Ed ora proviamo l'estrazione*/
select * from f_Fifo()

/*E il calcolo della valorizzazione complessiva*/

select Quantità=Sum(Quantità),
    Valore=Sum(Valore)
from f_Fifo()
go
/*
Come ci si aspettava il calcolo del Fifo risulta maggiore di suo fratello Lifo. 
L'indice creato precedentemente lavora per entrambi i calcoli
Tutto il codice riportato è scritto a braccio e senza appunti, non si prendano le affermazioni
fatte ne tantomeno le query per oro colato.
Tutto è passibile di verifica più dettagliata e naturalmente di miglioramenti. 
A questo proposito, se qulcuno avesse idee...
*/
go
/* Non ci resta che spazzolare da tempdb gli esempi creati*/
drop function f_FifoNext, f_FifoResidui, f_FifoArticolo, f_Fifo
drop function f_LifoNext, f_LifoResidui, f_LifoArticolo, f_Lifo
drop table t_Movimenti