10 de Septiembre de 2001

Introducción

    Este ejemplo pretende dar mi opinión sobre como tratar una gestión de acumulados de almacén desde Sql-Server y Visual-Basic. El ejemplo se basa en una pregunta al grupo de noticias de Sql-Server en Castellano y pretende resolver este punto en base a , de un lado tener acumulados de compras y ventas mensuales ágiles, y de otra parte tener estos mismos datos desde un punto de vista anual con la misma agilidad.
    Es de suponer que tambien se necesite tener un control del almacén desde un punto de vista global -¿Cuantos bolígrafos tengo?-, pero eso puede conserguirse bien desde estás mismas tablas, usando sentencias de agrupamiento por el criterio que se necesite, o bien incrementando la funcionalidad de los triggers que aquí se proponen para que acumulen también esa información. La decisión dependerá siempre del rendimiento que se espere del sistema…..
    Notad que no se ha tenido en cuenta multialmacén para el ejemplo y que se ha desnormalizado la relación Albaran-Lineas al objeto de tener más accesible la fecha ya que de otra forma complicaría en exceso el código y los detalles a tener en cuenta.     Yo creo que una desnormalización no es algo intrínsecamente negativo, existen casos como este que justifican la pérdida de espacio, por la agilidad y sencillez en el desarrollo. Tambien en este caso hay motivos de rendimiento evidentes que justifican esta desnormalización

La parte de Visual-Basic
    Una imagen vale más que mil palabras así que aquí tienes la pantallita que es lo que hace, ahora bien, si quieres descargarte todo el código haz click aquí

La parte de Sql-Server
    El código relativo al sql-server se divide a su vez en dos partes diferentes, la primera simplemente genera la estructura de tabla que soporta nuestro ejemplo, la segunda, contiene los dos triggers, realmente casi idénticos que aportarán esos acumulados mensuales y anuales. No he tenido en cuenta todos los colores que ofrece el sql-Query-analizer ya que es un pelín pesadito hacer esto en web….
El código de creación de tablas

if not object_id(‘Stocks’) is null
drop table stocks
go
if not object_id(‘StocksAño’) is null
drop table stocksaño
go
if not object_id(‘LineasAlbaranCompra’) is null
drop table lineasAlbaranCompra
go
if not object_id(‘LineasAlbaranVenta’) is null
drop table lineasAlbaranVenta
go

CREATE TABLE Stocks (año int not null, mes int not null,articulo int not null,
cantidadComprada float, ValorComprado float,
CantidadVendida float , ValorVendido float,
stock as isnull(CantidadComprada,0)-isnull(CantidadVendida,0))
go

create table StocksAño(año int not null, articulo int not null ,
cantidadComprada float, ValorComprado float,
CantidadVendida float , ValorVendido float,
stock as isnull(CantidadComprada,0)-isnull(CantidadVendida,0))
go

alter table Stocks add constraint pk_Compra primary key (año,mes,articulo)
go
alter table Stocksaño add constraint pk_compraaño primary key (año,articulo)
go

Create table LineasAlbaranCompra(id int not null, linea int not null,
Articulo int,Fecha datetime, Cantidad Float,
Precio Float )
go
alter table LineasAlbaranCompra add constraint pk_lac primary key (id,linea)

go
Create table LineasAlbaranVenta(id int not null, linea int not null,
Articulo int,Fecha datetime, Cantidad Float,
Precio Float )
go
alter table LineasAlbaranVenta add constraint pk_lav primary key (id,linea)
go

El código del trigger
    Observad que tiene suficientes comentarios para entender perfectamente lo que hace, por otra parte en el fichero zip teneis el código completo, aquí solo pondré uno de los dos triggers, ya que son practicamente igualitos.

create trigger trg_LineaAlbaranCompra on LineasAlbaranCompra
for insert,delete,update
as
begin

– ********************************************************************** –
– PRIMERO CON LAS INSERCIONES …………………………………. –
– ********************************************************************** –
– Primero intentamos actualizar las Stocks
– si no existe la clave primaria en el registro
– El update no hará nada, si si lo tiene actualizará los valores como yo le diga…

– en un update no se puede usar la clausula group by…..
– por lo que nos vemos obligados ha agruparlos antes…..
– Si estuviesemos en ss-2000 quizá podríamos usar variables
– en lugar de tablas temporales, pero las pruebas están hechas
– contra sql-server 7.0

select year(fecha) año ,month(fecha) mes, articulo,Sum(Cantidad) Cantidad ,sum(cantidad*Precio) Total
into #i
from inserted
group by year(inserted.fecha),month(inserted.fecha),articulo
– y ahora a actualizar…

– Primero a las Stocks mensuales ….
update Stocks Set CantidadComprada=isnull(CantidadComprada,0)+Cantidad,
ValorComprado = isNull(Valorcomprado,0)+Total
from #i Nuevas inner join Stocks
on Nuevas.Articulo=Stocks.articulo and
Nuevas.año = Stocks.año and
Nuevas.mes = Stocks.mes
– y después a las anuales…, el update es el mismo….
update StocksAño Set CantidadComprada=isnull(CantidadComprada,0)+Cantidad,
ValorComprado = isNull(Valorcomprado,0)+Total
from #i Nuevas inner join StocksAño
on Nuevas.Articulo=Stocksaño.articulo and
Nuevas.año = Stocksaño.año

– Algunas tendrán que ser insertadas ya que no existe el regitro
– de ese articulo en este año-mes, Luego haremos un insert into…
– Primero mensuales ….
insert into Stocks (año,mes,articulo,CantidadComprada,ValorComprado)
select nuevas.año,nuevas.mes,Nuevas.articulo,Cantidad,Total
from #i Nuevas left join Stocks
on Nuevas.Articulo=Stocks.articulo and
Nuevas.año = Stocks.año and
Nuevas.mes = Stocks.mes
where Stocks.articulo is null
– Después anuales ..
insert into StocksAño (año,articulo,CantidadComprada,ValorComprado)
select nuevas.año,Nuevas.articulo,Cantidad,Total
from #i Nuevas left join StocksAño
on Nuevas.Articulo=StocksAño.articulo and
Nuevas.año = StocksAño.año
where StocksAño.articulo is null

– lo que hago en esta insert es tener en cuenta que un not in que es lo
– que te pide el cuerpo (Insertar la que no estén ya), es un coste muy
– alto, luego se hace un join externo que devuelve nulos con mucho menor
– coste y se exige que el campo relacionado sea nulo, esto aunque es –
– básico conviene no olvidarlo….

– ********************************************************************** –
– AHORA CON LOS BORRADOS …………………………………. –
– ********************************************************************** –
– pasa lo mismo, el update no soporta group by por lo que lo tenemos que
– hacer un poquito antes….

select year(fecha) año ,month(fecha) mes, articulo,Sum(Cantidad) Cantidad ,sum(cantidad*Precio) Total
into #d
from deleted
group by year(fecha),month(fecha),articulo

– Primero mensuales …
update Stocks Set CantidadComprada=isnull(CantidadComprada,0)- Cantidad,
ValorComprado = isNull(Valorcomprado,0)- Total
from #d Viejos inner join Stocks
on Viejos.Articulo=Stocks.articulo and
Viejos.año = Stocks.año and
viejos.mes = Stocks.mes
– Luego Anuales …
update StocksAño Set CantidadComprada=isnull(CantidadComprada,0)- Cantidad,
ValorComprado = isNull(Valorcomprado,0)- Total
from #d Viejos inner join StocksAño
on Viejos.Articulo=StocksAño.articulo and
Viejos.año = StocksAño.año

– Supongo, aunque luego la experiencia te dice que no siempre es así que
– para borrar algo primero debe haberse insertado y si se ha insertado
– ya existe, luego no hace falta insertar el – lo que sea del campo Stocks
– Digo que la experiencia dice otra cosa ya que si un usuario toca a mano
– la tabla Stocks,….. cualquier cosa podría suceder y deberiamos tenerla
– contemplada…

end

go