TA - Northwind

----PREGUNTA 7------

CREATE FUNCTION Lista_Empleados(@Mes INT,@Anno INT)
RETURNS @Resultado TABLE (Names NVARCHAR(40), City NVARCHAR(15),Region NVARCHAR(15),PostalCode NVARCHAR(10),Country NVARCHAR(15), Ventas FLOAT, Compras FLOAT)
AS
BEGIN
DECLARE @prom AS FLOAT
SELECT @prom = SUM ((d.Quantity*d.unitprice)/d.Quantity)
FROM [Order Details] d INNER JOIN Orders o ON o.orderid=d.orderid
WHERE MONTH (o.orderdate)=@Mes AND Year (o.orderdate)=@Anno

INSERT INTO @Resultado

SELECT  E.LastName +' '+ E.FirstName AS Nombre,E.City,E.Region,E.PostalCode,E.Country,
            SUM(d.Quantity * d.unitprice) as Ventas,0.00 as Compras
    FROM
EMPLOYEES E INNER JOIN ORDERS O ON E.EmployeeID=O.EmployeeID INNER JOIN [Order Details] D ON O.orderid=D.orderid
WHERE
MONTH(O.orderdate)=@Mes AND YEAR (o.orderdate)=@Anno
GROUP BY E.LastName,E.FirstName ,E.City,E.Region,E.PostalCode,E.Country
HAVING SUM(D.Quantity*d.unitprice)>@prom
UNION
SELECT C.CompanyName as Nombre,C.City,C.Region,C.PostalCode,C.Country,
        0.00 as Ventas,sum(d.Quantity * d.unitprice) as Compras
       
    FROM
        CUSTOMERS C INNER JOIN ORDERS O ON C.CustomerID=O.CustomerID
        INNER JOIN [Order Details] D ON O.orderid=D.orderid
    WHERE MONTH(o.orderdate)=@Mes AND YEAR(o.orderdate)=@Anno
    GROUP BY C.CompanyName,C.City,C.Region,C.PostalCode,C.Country
    HAVING SUM(d.Quantity*d.unitprice)<@prom
    RETURN
   
    END
    GO

---PREGUNTA 8 -----

CREATE PROCEDURE uspProductoIUD (
    @ProductID int, @ProductName nvarchar(40), @SupplierID int,
    @CategoryID int, @QuantityPerUnit nvarchar(20),
    @UnitPrice money, @UnitsInStock smallint, @UnitsOnOrder smallint,
    @ReorderLevel smallint, @Discontinued bit,@Operacion int
)
AS BEGIN
    IF @Operacion = 1 BEGIN
        INSERT INTO Products
        (ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,
         UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued)
        VALUES
        (@ProductName,@SupplierID,@CategoryID,@QuantityPerUnit,
         @UnitPrice,@UnitsInStock,@UnitsOnOrder,@ReorderLevel,@Discontinued)
    END

    IF @Operacion = 2 BEGIN
        UPDATE Products
        SET      
            ProductName = @ProductName,SupplierID = @SupplierID,
            CategoryID = @CategoryID,QuantityPerUnit = @QuantityPerUnit,
            UnitPrice = @UnitPrice,UnitsInStock = @UnitsInStock,
            UnitsOnOrder = @UnitsOnOrder,ReorderLevel = @ReorderLevel,
            Discontinued = @Discontinued
        WHERE
            ProductID = @ProductID
    END

    IF @Operacion = 3 BEGIN
        DELETE FROM Products WHERE ProductID = @ProductID
    END
END

TA-Ahorros

--PREGUNTA 4--

CREATE PROCEDURE uspAbrir_Cuenta @COD_CLIENTE CHAR (5),@MONTO MONEY
AS
BEGIN
TRANSACTION
DECLARE @COD_CUENTA CHAR(5)
DECLARE @AUX INT

SELECT @COD_CUENTA=MAX(C_CUENTA) FROM CUENTA
IF @COD_CUENTA is NULL
BEGIN
SET @COD_CUENTA='C0001'
INSERT INTO CUENTA VALUES (@COD_CUENTA,@COD_CLIENTE,@MONTO)
INSERT INTO MOVIMIENTO VALUES (@COD_CUENTA,1,@MONTO)
end
else
BEGIN
SET @AUX= CONVERT (INT,RIGHT(@COD_CUENTA,4))+1
SET @COD_CUENTA = 'C' + RIGHT('0000'+CONVERT (VARCHAR,@AUX),4)
INSERT INTO CUENTA VALUES (@COD_CUENTA,@COD_CLIENTE,@MONTO)
INSERT INTO MOVIMIENTO VALUES (@COD_CUENTA,1,@MONTO)
end


COMMIT

exec uspAbrir_Cuenta 'C0010',200.00
exec uspAbrir_cuenta 'C0011',100.00

DROP PROCEDURE uspAbrir_Cuenta

SELECT *FROM CUENTA
select *from cliente

SELECT *FROM MOVIMIENTO



--PREGUNTA 5----


---PREGUNTA 5-------
CREATE PROCEDURE RETIRA_MONTO @CUENTA CHAR(5), @MONTO MONEY
AS
BEGIN
TRANSACTION

DECLARE @DINERO MONEY
SELECT @DINERO = C.S_SALDO
FROM CUENTA C
WHERE C.C_CUENTA = @CUENTA

BEGIN
DECLARE @NRO_MOV VARCHAR(1)
SELECT @NRO_MOV = MAX (C_MOV)+1
FROM MOVIMIENTO
WHERE C_CUENTA = @CUENTA

INSERT INTO MOVIMIENTO VALUES (@CUENTA,@NRO_MOV,@MONTO)

UPDATE CUENTA
SET S_SALDO = S_SALDO -@MONTO
WHERE C_CUENTA =@CUENTA
END
COMMIT


SELECT * FROM CUENTA

EXEC RETIRA_MONTO 'C0001', 50.0

SELECT * FROM CUENTA


--PREGUNTA 6--

CREATE PROCEDURE TRANSFERIR_MONTO @CUENTA_1 VARCHAR(5), @CUENTA_2 VARCHAR (5), @MONTO MONEY
AS
BEGIN TRANSACTION
DECLARE @SALDO MONEY
SELECT @SALDO = C.S_SALDO
FROM CUENTA C
WHERE C.C_CUENTA = @CUENTA_1

BEGIN
DECLARE @NRO_MOV int
SELECT @NRO_MOV = MAX (C_MOV)+1
FROM MOVIMIENTO
WHERE C_CUENTA = @CUENTA_1

INSERT INTO MOVIMIENTO VALUES(@CUENTA_1,@NRO_MOV,@MONTO)
UPDATE CUENTA
SET S_SALDO = S_SALDO + @MONTO
WHERE C_CUENTA = @CUENTA_2

UPDATE CUENTA
SET S_SALDO = S_SALDO - @MONTO
WHERE C_CUENTA = @CUENTA_1
COMMIT

END

SELECT * FROM CUENTA
SELECT * FROM MOVIMIENTO

EXEC TRANSFERIR_MONTO 'C0010','C0011',100.00

SELECT * FROM CUENTA
SELECT * FROM MOVIMIENTO

DROP PROCEDURE TRANSFERIR_MONTO

TA-Ventas

-------------PREGUNTA 1---------------

CREATE TRIGGER ACTUALIZA_STOCK ON VENTA_DETALLE FOR UPDATE
AS
BEGIN
DECLARE @CANTIDAD INT
DECLARE @CANTIDAD_2 INT
DECLARE @PRODUCTO INT

IF UPDATE (Q_CANTIDAD)
BEGIN
SELECT @CANTIDAD_2=Q_CANTIDAD,@PRODUCTO = C_PRODUCTO FROM INSERTED
SELECT @CANTIDAD = Q_CANTIDAD FROM DELETED

UPDATE PRODUCTO
SET Q_STOCK = Q_STOCK + @CANTIDAD-@CANTIDAD_2
WHERE C_PRODUCTO = @PRODUCTO

END
END

UPDATE VENTA_DETALLE

SET Q_CANTIDAD = 6
WHERE C_PRODUCTO = 1

SELECT * FROM PRODUCTO
select * from VENTA
SELECT * FROM CLIENTE
SELECT * FROM VENTA_DETALLE

------PREGUNTA 2-----------

-------PREGUNTA 2----------
CREATE TRIGGER ANULVENTA ON VENTA_DETALLE FOR DELETE
AS
DECLARE @CANTIDAD_PRODUCTO INT
DECLARE @PRECIO_UNITARIO MONEY

BEGIN
SELECT @CANTIDAD_PRODUCTO = Q_CANTIDAD FROM DELETED
SELECT @PRECIO_UNITARIO = S_PRECIO_UNITARIO FROM PRODUCTO
WHERE C_PRODUCTO = (SELECT C_PRODUCTO FROM DELETED)

UPDATE VENTA

SET S_VENTA_TOTAL = S_VENTA_TOTAL -@CANTIDAD_PRODUCTO*@PRECIO_UNITARIO
WHERE C_VENTA= (SELECT C_VENTA FROM DELETED)
END

DELETE FROM VENTA_DETALLE WHERE C_PRODUCTO = 1 AND C_VENTA = 1

SELECT *FROM VENTA_DETALLE
SELECT * FROM VENTA

---------PREGUNTA 3-----------


CREATE TRIGGER ACTUALIZA_STOCK_ANUL ON VENTA_DETALLE FOR DELETE
AS
BEGIN
DECLARE @CANTIDAD INT
DECLARE @C_PRODU INT

SELECT @CANTIDAD=Q_CANTIDAD FROM DELETED
SELECT @C_PRODU = C_PRODUCTO FROM DELETED

UPDATE PRODUCTO

SET
Q_STOCK =Q_STOCK + @CANTIDAD
WHERE
C_PRODUCTO = @C_PRODU
END

SELECT * FROM PRODUCTO
DELETE FROM VENTA_DETALLE WHERE C_PRODUCTO = 1 and C_VENTA = 1 AND Q_CANTIDAD = 2