USE master
GO

IF  EXISTS (
        SELECT name
                FROM sys.DATABASES
                WHERE name = N'AlexanderDubikovsky3'
)
ALTER DATABASE AlexanderDubikovsky3 SET single_user WITH ROLLBACK immediate
GO
DROP DATABASE AlexanderDubikovsky3
GO

CREATE DATABASE AlexanderDubikovsky3
GO

USE AlexanderDubikovsky3
GO

IF OBJECT_ID('Currensies', 'U') IS NOT NULL
        DROP TABLE Currensies
GO
CREATE TABLE Currensies
(
        id CHAR(3) NOT NULL,
        Name VARCHAR(30) NULL,
        CONSTRAINT PK_Currensies PRIMARY KEY (id)
)
GO

INSERT INTO Currensies
                VALUES
                ('RUB', N'Российский рубль'),
                ('USD', N'Американский доллар'),
                ('EUR', N'Евро'),
                ('GBP', N'Фунт стерлингов')
GO

IF OBJECT_ID('Rates', 'U') IS NOT NULL
        DROP TABLE Rates
GO
CREATE TABLE Rates
(
                id1 CHAR(3) NOT NULL,
                id2 CHAR(3) NOT NULL,
                Rate DECIMAL(5,1) NOT NULL,
                CONSTRAINT PK_Rates PRIMARY KEY (id1, id2),
                CONSTRAINT FK_Sold FOREIGN KEY (id1)
                REFERENCES Currensies(id),
                CONSTRAINT FK_Buy FOREIGN KEY (id2)
                REFERENCES Currensies(id)
)

INSERT INTO Rates
                VALUES
                ('USD', 'RUB', 45.6),
                ('RUB', 'USD', 0.0213),
                ('EUR', 'RUB', 59.31),
                ('RUB', 'EUR', 0.0171),
                ('GBP', 'RUB', 73.476),
                ('RUB', 'GBP', 0.0136),
                ('EUR', 'USD', 1.25),
                ('USD', 'EUR', 0.8),
                ('GBP', 'USD', 1.6),
                ('USD', 'GBP', 0.615),
                ('GBP', 'EUR', 1.229),
                ('EUR', 'GBP', 0.798),
                ('RUB', 'RUB', 1),
                ('USD', 'USD', 1),
                ('EUR', 'EUR', 1),
                ('GBP', 'GBP', 1)

GO

IF OBJECT_ID('Wallet', 'U') IS NOT NULL
        DROP TABLE Wallet
GO
CREATE TABLE Wallet
(
                CurID CHAR(3) NOT NULL,
                VALUE DECIMAL(38, 2) NULL,
                CONSTRAINT PK_Wallet PRIMARY KEY (CurID),
                CONSTRAINT FK_Wallet FOREIGN KEY (CurID)
                REFERENCES Currensies(id)
)
GO

INSERT INTO Wallet
                VALUES
                ('RUB', 1000),
                ('USD', 23.75),
                ('EUR', 205),
                ('GBP', 75.5)
GO

SELECT Currensies.Name AS 'Валюта', Wallet.VALUE AS 'Сумма в кошельке'
FROM Wallet
INNER JOIN Currensies
ON Currensies.id = Wallet.CurID
GROUP BY Currensies.Name, Wallet.VALUE
GO

IF OBJECT_ID (N'ConvertedValue') IS NOT NULL
DROP FUNCTION ConvertedValue
GO
CREATE FUNCTION ConvertedValue(@CurCode CHAR(3))
RETURNS DECIMAL(38,2)
WITH EXECUTE AS CALLER
AS
BEGIN
        DECLARE @VALUE DECIMAL(38,2)
        SET @VALUE = (
                SELECT SUM(Wallet.VALUE*Rates.Rate)
                FROM Wallet
                INNER JOIN Rates
                ON (Wallet.CurID = Rates.id1)
                WHERE Rates.id2 = @CurCode
        )
        RETURN @VALUE
END
GO

IF OBJECT_ID (N'AddMoney') IS NOT NULL
DROP PROCEDURE AddMoney
GO
CREATE PROCEDURE AddMoney @CurCode CHAR(3), @Quantity DECIMAL(38,2)
AS
BEGIN
DECLARE @temp DECIMAL(38,2)
IF EXISTS
(
SELECT VALUE FROM Wallet
WHERE CurID = @CurCode
)
BEGIN
UPDATE Wallet
SET VALUE = (SELECT VALUE FROM Wallet
WHERE CurID = @CurCode) + @Quantity
WHERE CurID = @CurCode
END
ELSE
BEGIN
INSERT INTO Wallet
        VALUES
        (@CurCode, @Quantity)
END
END

SELECT dbo.ConvertedValue('RUB') AS 'В кошельке рублей:'
SELECT dbo.ConvertedValue('USD') AS 'В кошельке долларов:'
SELECT dbo.ConvertedValue('EUR') AS 'В кошельке евро:'
SELECT dbo.ConvertedValue('GBP') AS 'В кошельке фунтов стерлингов:'
GO

--IF OBJECT_ID (N'TakeMoney') IS NOT NULL
--DROP PROCEDURE TakeMoney
--GO
--CREATE PROCEDURE TakeMoney @CurCode CHAR(3), @Quantity DECIMAL(38,2)
--AS
--BEGIN
--IF NOT EXISTS
--(
--SELECT VALUE FROM Wallet
--WHERE CurID = @CurCode
--)
--BEGIN
--PRINT N'В кошельке нет такой валюты'
--END
--ELSE
--BEGIN
--IF (SELECT VALUE FROM Wallet
--WHERE CurID = @CurCode) >= @Quantity
--BEGIN
--UPDATE Wallet
--SET VALUE = (SELECT VALUE FROM Wallet
--WHERE CurID = @CurCode) - @Quantity
--WHERE CurID = @CurCode
--END
--ELSE
--BEGIN
--PRINT N'Недостаточно денего данной валюты'
--END
--END
--END

EXECUTE AddMoney 'USD', 50

--SELECT id1,[EUR], [GBP],[RUB], [USD]
--FROM (
--        SELECT id1, id2, Rate
--        FROM Rates
--        GROUP BY id1, id2, Rate
--        ) AS p
--PIVOT
--(
--SUM(Rate)
--FOR id2
--IN([GBP], [USD], [EUR], [RUB])
--) AS pvt
--GO