query.sql

IF OBJECT_ID('PURCHASEORDER6925') IS NOT NULL
DROP TABLE PURCHASEORDER6925;

IF OBJECT_ID('INVENTORY6925') IS NOT NULL
DROP TABLE INVENTORY6925;

IF OBJECT_ID('ORDERLINE6925') IS NOT NULL
DROP TABLE ORDERLINE6925;

IF OBJECT_ID('ORDER6925') IS NOT NULL
DROP TABLE [ORDER6925];

IF OBJECT_ID('AUTHORISEDPERSON6925') IS NOT NULL
DROP TABLE AUTHORISEDPERSON6925;

IF OBJECT_ID('ACCOUNTPAYMENT6925') IS NOT NULL
DROP TABLE ACCOUNTPAYMENT6925;

IF OBJECT_ID('CLIENTACCOUNT6925') IS NOT NULL
DROP TABLE CLIENTACCOUNT6925;

IF OBJECT_ID('PRODUCT6925') IS NOT NULL
DROP TABLE PRODUCT6925;

IF OBJECT_ID('LOCATION6925') IS NOT NULL
DROP TABLE [LOCATION6925];

IF OBJECT_ID('GENERALLEDGER6925') IS NOT NULL
DROP TABLE GENERALLEDGER6925;

GO

CREATE TABLE GENERALLEDGER6925(
    ITEMID INTEGER,
    DESCRIPTION NVARCHAR(100),
    AMOUNT MONEY,
    CONSTRAINT PK_GENERALLEDGER PRIMARY KEY (ITEMID),
    CONSTRAINT UQ_GENERALEDGER_DESCRIPTION UNIQUE(DESCRIPTION)
);

INSERT INTO GENERALLEDGER6925 (ITEMID, DESCRIPTION, AMOUNT) VALUES
(1, 'ASSETSCASH', 100000.00),
(2, 'ASSETSSTOCK', 0),
(3, 'ASSETSACCOUNT', 0);

CREATE TABLE [LOCATION6925](
    LOCATIONID NVARCHAR(8),
    LOCNAME NVARCHAR(50) NOT NULL,
    ADDRESS NVARCHAR(200) NOT NULL,
    MANAGER NVARCHAR(100),
    CONSTRAINT PK_LOCATION PRIMARY KEY (LOCATIONID)
);

CREATE TABLE PRODUCT6925(
    PRODUCTID INTEGER IDENTITY(10001, 1),
    PRODNAME NVARCHAR(100) NOT NULL,
    BUYPRICE MONEY,
    SELLPRICE MONEY,
    CONSTRAINT PK_PRODUCT PRIMARY KEY(PRODUCTID),
    CONSTRAINT CHK_WHOLESALE_RETAIL CHECK(BUYPRICE < SELLPRICE)
);

CREATE TABLE CLIENTACCOUNT6925(
    ACCOUNTID INTEGER IDENTITY(30001, 1),
    ACCTNAME NVARCHAR(100) NOT NULL,
    BALANCE MONEY NOT NULL,
    CREDITLIMIT MONEY NOT NULL,
    CONSTRAINT PK_CLIENTACCOUNT PRIMARY KEY(ACCOUNTID),
    CONSTRAINT CHK_CLIENTACCOUNT_BALANCE_CREDIT CHECK(BALANCE<=CREDITLIMIT),
    CONSTRAINT UQ_CLENTACCOUNT_NAME UNIQUE(ACCTNAME)
);

CREATE TABLE ACCOUNTPAYMENT6925(
    ACCOUNTID INTEGER,
    DATETIMERECEIVED DATETIME,
    AMOUNT MONEY NOT NULL,
    CONSTRAINT PK_ACCOUNTPAYMENT PRIMARY KEY(ACCOUNTID, DATETIMERECEIVED),
    CONSTRAINT FK_ACCOUNTPAYMENT_ACCOUNT FOREIGN KEY (ACCOUNTID) REFERENCES CLIENTACCOUNT6925,
    CONSTRAINT CHK_ACCOUNTPAYMENT_AMOUNT CHECK(AMOUNT >0)
);

CREATE TABLE AUTHORISEDPERSON6925(
    USERID INTEGER IDENTITY(50001, 1),
    FIRSTNAME NVARCHAR(100) NOT NULL,
    SURNAME NVARCHAR(100) NOT NULL,
    EMAIL NVARCHAR(100) NOT NULL,
    [PASSWORD] NVARCHAR(100) NOT NULL,
    ACCOUNTID INTEGER NOT NULL,
    CONSTRAINT PK_AUTHORISEDPERSON PRIMARY KEY(USERID),
    CONSTRAINT FK_AUTHORISEDPERSON_CLIENTACCOUNT FOREIGN KEY(ACCOUNTID) REFERENCES CLIENTACCOUNT6925,
    CONSTRAINT CHK_AUTHORISEDPERSON_EMAIL CHECK(EMAIL LIKE '%@%')
);

CREATE TABLE [ORDER6925](
    ORDERID INTEGER IDENTITY(70001, 1),
    SHIPPINGADDRESS NVARCHAR(200) NOT NULL,
    DATETIMECREATED DATETIME NOT NULL,
    DATETIMEDISPATCHED DATETIME,
    TOTAL MONEY NOT NULL,
    USERID INTEGER NOT NULL,
    CONSTRAINT PK_ORDER PRIMARY KEY(ORDERID),
    CONSTRAINT FK_ORDER_AUTHORISEDPERSON FOREIGN KEY(USERID) REFERENCES AUTHORISEDPERSON6925,
    CONSTRAINT CHK_ORDER_TOTAL CHECK(TOTAL >= 0)
);


CREATE TABLE ORDERLINE6925(
    ORDERID INTEGER,
    PRODUCTID INT,
    QUANTITY INT NOT NULL,
    DISCOUNT DECIMAL DEFAULT 0,
    SUBTOTAL MONEY NOT NULL,
    CONSTRAINT PK_ORDERLINE PRIMARY KEY(ORDERID, PRODUCTID),
    CONSTRAINT FK_ORDERLINE_ORDER FOREIGN KEY(ORDERID) REFERENCES [ORDER6925],
    CONSTRAINT FK_ORDERLINE_PRODUCT FOREIGN KEY(PRODUCTID) REFERENCES PRODUCT6925,
    CONSTRAINT CHK_ORDER_DISCOUNT CHECK(DISCOUNT >=0 AND DISCOUNT <= 0.25),
    CONSTRAINT CHK_ORDERLINE_SUBTOTAL CHECK(SUBTOTAL > 0)
);

CREATE TABLE INVENTORY6925(
    PRODUCTID INT,
    LOCATIONID NVARCHAR(8),
    NUMINSTOCK INTEGER NOT NULL,
    CONSTRAINT PK_INVENTORY PRIMARY KEY(PRODUCTID, LOCATIONID),
    CONSTRAINT FK_INVENTORY_PRODUCT FOREIGN KEY(PRODUCTID) REFERENCES PRODUCT6925,
    CONSTRAINT FK_INVENTORY_LOCATION FOREIGN KEY(LOCATIONID) REFERENCES LOCATION6925,
    CONSTRAINT CHK_INVENTORY_NUMINSTOCK CHECK(NUMINSTOCK >=0)
);

CREATE TABLE PURCHASEORDER6925(
    PRODUCTID INT,
    LOCATIONID NVARCHAR(8),
    DATETIMECREATED DATETIME,
    QUANTITY INTEGER,
    TOTAL MONEY,
    CONSTRAINT PK_PURCHASEORDER PRIMARY KEY(PRODUCTID, LOCATIONID, DATETIMECREATED),
    CONSTRAINT FK_PURCHASEORDER_PRODUCT FOREIGN KEY(PRODUCTID) REFERENCES PRODUCT6925,
    CONSTRAINT FK_PURCHASEORDER_LOCATION FOREIGN KEY(LOCATIONID) REFERENCES LOCATION6925,
    CONSTRAINT CHK_PURCHASEORDER_QUANTITY CHECK(QUANTITY > 0)
);

GO


--SELECT * FROM SYS.TABLES;

--------------------------

-- SET UP LOCATION, PRODUCT AND INVENTORY
BEGIN

    INSERT INTO LOCATION6925(LOCATIONID, LOCNAME, ADDRESS, MANAGER)VALUES
    ('MLB3931', 'Melbourne South East', '123 Demon Street, Mornington, 3931', 'Bruce Wayne');

    INSERT INTO PRODUCT6925(PRODNAME, BUYPRICE, SELLPRICE) VALUES
    ('APPLE ME PHONE X', '890.00', 1295.00 );

    DECLARE @PRODID INT = @@IDENTITY;

    INSERT INTO INVENTORY6925(PRODUCTID, LOCATIONID, NUMINSTOCK) VALUES
    (@PRODID, 'MLB3931', 0);

    -- ADD A NEW CLIENT ACCOUNT AND A NEW AUTHORISED USER FOR THAT ACCOUNT

    INSERT INTO CLIENTACCOUNT6925(ACCTNAME, BALANCE, CREDITLIMIT) VALUES
    ('FREDS LOCAL PHONE STORE', '0', 10000.00 );

    DECLARE @ACCOUNTID INT = @@IDENTITY;

    INSERT INTO AUTHORISEDPERSON6925(FIRSTNAME, SURNAME, EMAIL, [PASSWORD], ACCOUNTID) VALUES
    ('Fred', 'Flintstone', 'fred@fredsphones.com', 'secret', @ACCOUNTID);

    DECLARE @USERID INT = @@IDENTITY;

    -----------

    -- BUY SOME STOCK

    -- ADD A PURCHASE ORDER ROW
    INSERT INTO PURCHASEORDER6925(PRODUCTID, LOCATIONID, DATETIMECREATED, QUANTITY, TOTAL) VALUES
    (@PRODID,  'MLB3931', '10-Apr-2020', 50, 44500.00);

    -- UPDATE OUR INVENTORY FOR THAT STOCK
    UPDATE INVENTORY6925 SET NUMINSTOCK = 50 WHERE PRODUCTID = @PRODID AND LOCATIONID = 'MLB3931';

    -- UPDATE THE GENERAL LEDGER INCREASING THE VALUE OF OUR STOCK ASSETS AND DECREASING THE CASH ASSETS
    UPDATE GENERALLEDGER6925 SET AMOUNT = AMOUNT - 44500.00 WHERE DESCRIPTION = 'ASSETSCASH';
    UPDATE GENERALLEDGER6925 SET AMOUNT = AMOUNT + 44500.00 WHERE DESCRIPTION = 'ASSETSSTOCK';

    -----------

    -- CUSTOMER MAKES AN ORDER - (INITIALLY THE ORDER IS NOT FULFILLED)

    INSERT INTO ORDER6925(SHIPPINGADDRESS, DATETIMECREATED, DATETIMEDISPATCHED, TOTAL, USERID) VALUES
    ('7 Lucky Strike, Bedrock, USB, 1111', '20-Apr-2020', NULL, 6151.25, @USERID);

    DECLARE @ORDERID INT = @@IDENTITY;

    INSERT INTO ORDERLINE6925(ORDERID, PRODUCTID, QUANTITY, DISCOUNT, SUBTOTAL) VALUES
    (@ORDERID, @PRODID, 5, 0.05, '6151.25');

    -- WE FULLFILL THE ORDER

    -- UPDATE THE ORDER TO GIVE IT A FULLFUILLED DATE
    UPDATE ORDER6925 SET DATETIMEDISPATCHED = '21-Apr-2020' WHERE ORDERID = @ORDERID;

    -- UPDATE THE CLIENTS ACCOUNT BALANCE TO INCLUDE THE VALUE OF THE ORDER
    UPDATE CLIENTACCOUNT6925 SET BALANCE = BALANCE + 6151.25 WHERE ACCOUNTID = @ACCOUNTID;

    -- UPDATE THE GENERAL LEDGER INCREASING VALUE OF ACCOUNTS, DECEASING VALUE OF STOCK
    UPDATE GENERALLEDGER6925 SET AMOUNT = AMOUNT + 6151.25  WHERE DESCRIPTION = 'ASSETSACCOUNT';
    UPDATE GENERALLEDGER6925 SET AMOUNT = AMOUNT - (5*890) WHERE DESCRIPTION = 'ASSETSSTOCK';

    -------------

    -- CLIENT MAKES AN ACCOUNT OFF THIER ACCOUNT BALANCE

    -- ADD A ROW TO ACCOUNTPAYMENT6925
    INSERT INTO ACCOUNTPAYMENT6925(ACCOUNTID, DATETIMERECEIVED, AMOUNT) VALUES
        (@ACCOUNTID, '25-Apr-2020', '2000.00');

    -- UPDATE THE CLIENT ACCOUNT TO REFLECT THE BALANCE CHANGE
    UPDATE CLIENTACCOUNT6925 SET BALANCE = BALANCE - 2000.00 WHERE ACCOUNTID = @ACCOUNTID;

    -- UPDATE THE GENERAL LEDGER - INCREASE ASSETSCASH AND DECREASE ASSETS ACCOUNT
    UPDATE GENERALLEDGER6925 SET AMOUNT = AMOUNT + 2000.00 WHERE DESCRIPTION = 'ASSETSCASH';
    UPDATE GENERALLEDGER6925 SET AMOUNT = AMOUNT - 2000.00 WHERE DESCRIPTION = 'ASSETSACCOUNT';
END;

GO
----------------------------

























IF OBJECT_ID('ADD_LOCATION') IS NOT NULL
DROP PROCEDURE ADD_LOCATION
GO

CREATE PROCEDURE ADD_LOCATION @PLOCID NVARCHAR(50), @PLOCNAME NVARCHAR(50), @PLOCADDRESS NVARCHAR(200), @PMANAGER NVARCHAR(100) AS
BEGIN

    -- THE FOLLOWING MUST BE COMPLETED AS A SINGLE TRANSACTION
    -- insert the specified values into the table LOCATION6925
    -- ADD A ROW FOR THIS LOCATION TO THE INVENTORY6925 TABLE **FOR EACH** PRODUCT IN THE PRODUCT6925 TABLE
    -- I.E. IF THERE ARE 4 PRODUCTS THIS WILL BE 4 NEW ROWS IN THE INVENTORY TABLE
    -- RETURN THE LOCID OF THE NEW LOCATION

    -- EXCEPTIONS
    -- if the location id is a duplicate throw error: number 51001  message : 'Duplicate Location ID'
    -- for any other errors throw error : number 50000  message:  error_message()

BEGIN TRY
    INSERT INTO LOCATION6925(LOCATIONID,LOCNAME,ADDRESS,MANAGER)
    VALUES (@PLOCID,@PLOCNAME,@PLOCADDRESS, @PMANAGER);
    DECLARE CUR2 CURSOR FOR SELECT PRODUCTID FROM PRODUCT6925;
    DECLARE @PRODID INT;
    OPEN CUR2 FETCH NEXT FROM CUR2 INTO @PRODID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO INVENTORY6925(PRODUCTID,LOCATIONID,NUMINSTOCK) VALUES
        (@PRODID, @PLOCID, 0)
        FETCH NEXT FROM CUR2 INTO @PRODID
    END
    CLOSE CUR2
    DEALLOCATE CUR2
    SELECT * FROM LOCATION6925 WHERE LOCATIONID = @PLOCID
END TRY
BEGIN CATCH
    DECLARE @ERRORMESSAGE NVARCHAR(MAX)= ERROR_MESSAGE();

    DECLARE @ERRORNUM INT= ERROR_NUMBER();

    IF ERROR_NUMBER() = 2627
        THROW 51001, 'Duplicate Location ID', 1
    ELSE
    IF ERROR_NUMBER() > 50000
    THROW @ERRORNUM, @ERRORMESSAGE, 1
    ELSE
    THROW 50000, @ERRORMESSAGE, 1
END CATCH;


END
GO


EXEC ADD_LOCATION 5, 'Sample Text','Sample Text 2', 1234
SELECT * FROM LOCATION6925;
GO

IF OBJECT_ID('GET_LOCATION_BY_ID') IS NOT NULL
DROP PROCEDURE GET_LOCATION_BY_ID 
GO


CREATE PROCEDURE GET_LOCATION_BY_ID @PLOCID NVARCHAR(100) AS -- type of PLCOID was wrong!!!!!! changed it
BEGIN
BEGIN TRY
/*
    -- return the specified location.

    -- EXCEPTIONS
    -- if the location id is invalid throw error: number 51002  message : 'Location Doesnt Exist'
    -- for any other errors throw error : number 50000  message:  error_message()
END;
*/
IF EXISTS(SELECT * FROM LOCATION6925 WHERE LOCATIONID = @PLOCID)
    SELECT * FROM LOCATION6925 WHERE LOCATIONID = @PLOCID;
ELSE
    THROW 51002, 'Location Doesnt Exist', 1
END TRY
BEGIN CATCH
    DECLARE @ERRORMESSAGE NVARCHAR(MAX)= ERROR_MESSAGE();

    DECLARE @ERRORNUM INT= ERROR_NUMBER();

    IF ERROR_NUMBER() > 50000
    THROW @ERRORNUM, @ERRORMESSAGE, 1
    ELSE
    THROW 50000, @ERRORMESSAGE, 1
END CATCH;
END
GO

EXEC GET_LOCATION_BY_ID 5
GO


IF OBJECT_ID('ADD_PRODUCT') IS NOT NULL
DROP PROCEDURE ADD_PRODUCT ;
GO

/*
CREATE PROCEDURE ADD_PRODUCT @PPRODNAME NVARCHAR(100), @PBUYPRICE MONEY, @PSELLPRICE MONEY AS
BEGIN

    -- THE FOLLOWING MUST BE COMPLETED AS A SINGLE TRANSACTION
    -- insert the specified values into the table PRODUCT6925
    -- ADD A ROW FOR THIS PRODUCT TO THE INVENTORY6925 TABLE **FOR EACH** LOCTAION IN THE LOCATION6925 TABLE
    -- I.E. IF THERE ARE 4 LOCATIONS THIS WILL BE 4 NEW ROWS IN THE INVENTORY TABLE
    -- RETURN THE NEW PRODUCTS PRODUCTID

    -- EXCEPTIONS
    -- for any other errors throw error : number 50000  message:  error_message()
END;
*/
CREATE PROCEDURE ADD_PRODUCT @PPRODNAME NVARCHAR(100), @PBUYPRICE MONEY, @PSELLPRICE MONEY AS
BEGIN
BEGIN TRY
    INSERT INTO PRODUCT6925(PRODNAME,BUYPRICE,SELLPRICE) VALUES (@PPRODNAME, @PBUYPRICE,@PSELLPRICE);
    DECLARE @PRODID INT;
    SELECT @PRODID = MAX(PRODUCTID) FROM PRODUCT6925;
    DECLARE CUR3 CURSOR FOR SELECT LOCATIONID FROM LOCATION6925;
    DECLARE @LOCID NVARCHAR(100);
    OPEN CUR3 FETCH NEXT FROM CUR3 INTO @LOCID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO INVENTORY6925(PRODUCTID,LOCATIONID,NUMINSTOCK)
        VALUES (@PRODID,@LOCID,0)
        FETCH NEXT FROM CUR3 INTO @LOCID
    END
    CLOSE CUR3
    DEALLOCATE CUR3
END TRY
BEGIN CATCH
    DECLARE @ERRORMESSAGE NVARCHAR(MAX)= ERROR_MESSAGE();

    DECLARE @ERRORNUM INT= ERROR_NUMBER();


    THROW 50000, @ERRORMESSAGE, 1
END CATCH;

END
GO


EXEC ADD_PRODUCT "SAMPLE",10,15
SELECT * FROM PRODUCT6925;
GO

IF OBJECT_ID('GET_PRODUCT_BY_ID') IS NOT NULL
DROP PROCEDURE GET_PRODUCT_BY_ID ;
GO



/*
CREATE PROCEDURE GET_PRODUCT_BY_ID @PPRODID INT AS
BEGIN
    -- return the specified PRODUCT.

    -- EXCEPTIONS
    -- if the PRODUCT id iis invalid throw error: number 52002  message : 'Product Doesnt Exist'
    -- for any other errors throw error : number 50000  message:  error_message()
END;
*/
CREATE PROCEDURE GET_PRODUCT_BY_ID @PPRODID INT AS
BEGIN
BEGIN TRY

IF EXISTS(SELECT * FROM PRODUCT6925 WHERE PRODUCTID = @PPRODID)
    SELECT * FROM PRODUCT6925 WHERE PRODUCTID = @PPRODID;
ELSE
    THROW 51002, 'Location Doesnt Exist', 1
END TRY
BEGIN CATCH
    DECLARE @ERRORMESSAGE NVARCHAR(MAX)= ERROR_MESSAGE();

    DECLARE @ERRORNUM INT= ERROR_NUMBER();

    IF ERROR_NUMBER() > 50000
    THROW @ERRORNUM, @ERRORMESSAGE, 1
    ELSE
    THROW 50000, @ERRORMESSAGE, 1
END CATCH;
END
GO

EXEC GET_PRODUCT_BY_ID 10002
GO

IF OBJECT_ID('PURCHASE_STOCK') IS NOT NULL
DROP PROCEDURE PURCHASE_STOCK;
GO

/*
CREATE PROCEDURE PURCHASE_STOCK @PPRODID INT, @PLOCID INT, @PQTY INT AS
BEGIN

    -- THE FOLLOWING MUST BE COMPLETED AS A SINGLE TRANSACTION

    -- insert the A ROW TO THE PURCHASE ORDER TABLE
    -- USE THE CURRENT SYSTEM DATETIME AS FOR THE DATETIMECREATED FIELD
    -- CALCULATE THE TOTAL BASED ON THE BUYPRICE OF THE PRODUCT SPECIFICED AND THE QUANTITY IN @PQTY
    -- UPDATE INVENTORY6925 FOR THE SPECIFIED PRODUCT IN THE SPECIFIED LOCATION BY THE QTY PURCHASED
    -- DECREASE THE ASSETCASH ROW IN THE GENERAL LEDGER BY THE TOTAL AMOUNT OF THE ORDER
    -- INCREASE THE ASSETSTOCK ROW IN THE GENERAL LEDGER BY THE TOTAL AMOUNT OF THE ORDER

    -- EXCEPTIONS
    -- if the LOCATUON id is invalid throw error: number 51002  message : 'Location Doesnt Exist'
    -- if the PRODUCT id is invalid throw error: number 52002  message : 'Product Doesnt Exist'
    -- IF THERE IS INSUFFICIENT ASSETSCASH IN THE GENERAL LEDGER THEN THROW ERROR: 59001 MESSAGE : 'INSUFFICIENT CASH'
    -- for any other errors throw error : number 50000  message:  error_message()
END;
*/
CREATE PROCEDURE PURCHASE_STOCK @PPRODID INT, @PLOCID NVARCHAR(100), @PQTY INT AS
BEGIN
BEGIN TRY

IF NOT EXISTS (SELECT * FROM LOCATION6925 WHERE LOCATIONID = @PLOCID) THROW 51002, 'LOCATION DOESNT EXIST', 1
IF NOT EXISTS (SELECT * FROM PRODUCT6925 WHERE PRODUCTID = @PPRODID) THROW 52002, 'PRODUCT DOESNT EXIST',1


DECLARE @price MONEY SELECT @price = SELLPRICE FROM PRODUCT6925 WHERE PRODUCTID = @PPRODID;
DECLARE @currentcash MONEY SELECT @currentcash = AMOUNT FROM GENERALLEDGER6925 WHERE DESCRIPTION = 'ASSETSCASH'
DECLARE @currentstock MONEY SELECT @currentstock = AMOUNT FROM GENERALLEDGER6925 WHERE DESCRIPTION = 'ASSETSSTOCK'
DECLARE @currentaccount MONEY SELECT @currentaccount = AMOUNT FROM GENERALLEDGER6925 WHERE DESCRIPTION = 'ASSETSACCOUNT'
DECLARE @current INT SELECT @current = NUMINSTOCK FROM INVENTORY6925 WHERE PRODUCTID = @PPRODID;
DECLARE @total MONEY = @price * @PQTY;

IF (@price > @currentcash) THROW 59001, 'INSUFFICIENT CASH', 1

INSERT INTO PURCHASEORDER6925(PRODUCTID, LOCATIONID,DATETIMECREATED,QUANTITY,TOTAL) VALUES (@PPRODID, @PLOCID, GETDATE(),@PQTY, @total)

UPDATE INVENTORY6925 SET NUMINSTOCK = @current + @PQTY WHERE PRODUCTID = @PPRODID;

UPDATE GENERALLEDGER6925 SET AMOUNT = @currentcash - @total WHERE [DESCRIPTION] = 'ASSETSCASH'
UPDATE GENERALLEDGER6925 SET AMOUNT = @currentcash + @total WHERE [DESCRIPTION] = 'ASSETSSTOCK'

END TRY
BEGIN CATCH
    DECLARE @ERRORMESSAGE NVARCHAR(MAX)= ERROR_MESSAGE();

    DECLARE @ERRORNUM INT= ERROR_NUMBER();

    IF ERROR_NUMBER() > 50000
    THROW @ERRORNUM, @ERRORMESSAGE, 1
    ELSE
    THROW 50000, @ERRORMESSAGE, 1
END CATCH;
END
GO

EXEC PURCHASE_STOCK 10001, 5, 5
SELECT * FROM INVENTORY6925
GO

IF OBJECT_ID('ADD_CLIENT_ACCOUNT') IS NOT NULL
DROP PROCEDURE ADD_CLIENT_ACCOUNT;
GO

/*
CREATE PROCEDURE ADD_CLIENT_ACCOUNT @PACCTNAME NVARCHAR(100), @PBALANCE MONEY, @PCREDITLIMIT MONEY AS
BEGIN

    -- insert the specified values into the table CLIENTACCOUNT6925
    -- RETURN THE NEW ACCOUNTS ACCOUNTID

    -- EXCEPTIONS
    -- ACCOUNT NAME ALREADY EXISTS - SEE TABLE CONSTRAINTS - THROW ERROR 53001 : DUPLICATE ACCOUNT NAME
    -- for any other errors throw error : number 50000  message:  error_message()
END;
*/
CREATE PROCEDURE ADD_CLIENT_ACCOUNT @PACCTNAME NVARCHAR(100), @PBALANCE MONEY, @PCREDITLIMIT MONEY AS
BEGIN

BEGIN TRY

IF EXISTS (SELECT * FROM CLIENTACCOUNT6925 WHERE ACCTNAME = @PACCTNAME) THROW 53001, 'DUPLICATE ACCOUNT NAME', 1

INSERT INTO CLIENTACCOUNT6925(ACCTNAME, BALANCE, CREDITLIMIT) VALUES (@PACCTNAME,@PBALANCE,@PCREDITLIMIT);
SELECT @@IDENTITY;

END TRY
BEGIN CATCH
    DECLARE @ERRORMESSAGE NVARCHAR(MAX)= ERROR_MESSAGE();

    DECLARE @ERRORNUM INT= ERROR_NUMBER();

    IF ERROR_NUMBER() > 50000
    THROW @ERRORNUM, @ERRORMESSAGE, 1
    ELSE
    THROW 50000, @ERRORMESSAGE, 1
END CATCH;
END
GO

EXEC ADD_CLIENT_ACCOUNT 'JORDAN F',1000,10000
SELECT * FROM CLIENTACCOUNT6925
GO



IF OBJECT_ID('ADD_AUTHORISED_PERSON') IS NOT NULL
DROP PROCEDURE ADD_AUTHORISED_PERSON;

GO

/*
CREATE PROCEDURE ADD_AUTHORISED_PERSON @PFIRSTNAME NVARCHAR(100), @PSURNAME NVARCHAR(100), @PEMAIL NVARCHAR(100), @PPASSWORD NVARCHAR(100), @PACCOUNTID INT AS
BEGIN

    -- insert the specified values into the table AUTHORISEDPERSON6925
    -- RETURN THE NEW USERS USER ID

    -- EXCEPTIONS
    -- EMAIL IS INVALID (DOESN'T CONTAIN AN @ - SEE TABLE CONSTRAINTS)  - THROW ERROR 53003 : INVALID EMAIL ADDRESS
    -- for any other errors throw error : number 50000  message:  error_message()
END;
*/
CREATE PROCEDURE ADD_AUTHORISED_PERSON @PFIRSTNAME NVARCHAR(100), @PSURNAME NVARCHAR(100), @PEMAIL NVARCHAR(100), @PPASSWORD NVARCHAR(100), @PACCOUNTID INT AS
BEGIN
BEGIN TRY

    IF (@PEMAIL not like '%@%') THROW 53003, 'INVALID EMAIL ADDRESS', 1
    
    INSERT INTO AUTHORISEDPERSON6925(FIRSTNAME,SURNAME,EMAIL,[PASSWORD],ACCOUNTID) VALUES (@PFIRSTNAME,@PSURNAME, @PEMAIL, @PPASSWORD, @PACCOUNTID)
    RETURN @@IDENTITY

END TRY
BEGIN CATCH
    DECLARE @ERRORMESSAGE NVARCHAR(MAX)= ERROR_MESSAGE();

    DECLARE @ERRORNUM INT= ERROR_NUMBER();

    IF ERROR_NUMBER() > 50000
    THROW @ERRORNUM, @ERRORMESSAGE, 1
    ELSE
    THROW 50000, @ERRORMESSAGE, 1
END CATCH;
END
GO

EXEC ADD_AUTHORISED_PERSON 'JORDAN','FERRAZZA','JORDANFERRAZZA@GMAIL.COM','1234', 30002
SELECT * FROM AUTHORISEDPERSON6925
GO

IF OBJECT_ID('MAKE_ACCOUNT_PAYMENT') IS NOT NULL
DROP PROCEDURE MAKE_ACCOUNT_PAYMENT;

GO

/*
CREATE PROCEDURE MAKE_ACCOUNT_PAYMENT @PACCOUNTID INT, @PAMOUNT MONEY AS
BEGIN

    -- THE FOLLOWING MUST BE COMPLETED AS A SINGLE TRANSACTION
    -- insert the specified values into the table ACCOUNTPAYMENT6925 (USING THE CURRENT SYS DATETIME)
    -- UPDATE THE RELEVANT ACCOUNT IN CLENTACCOUNT6925 TO RELFECT THE BALANCE REDUCED BY THE PAYMENT
    -- UPDATE THE GENERAL LEDGER TO REDUCE ACCOUNT ASSETS BY THE PAYMENT AMOUNT
    -- UPDATE THE GENERAL LEDGER TO INCREASE CASH ASSETS BY THE PAYMENT AMOUNT

    -- EXCEPTIONS
    -- ACCOUNT DOESNT EXIST THROW ERROR 53002 : ACCOUNT DOES NOT EXIST 
    -- PAYMENT AMOUNT IS NEGATIVE (SEE TABLE CONSTRAINTS) THROW ERROR 53004 :   ACCOUNT PAYMENT AMOUNT MUST BE POSITIVE  
    -- for any other errors throw error : number 50000  message:  error_message()
END;
*/
CREATE PROCEDURE MAKE_ACCOUNT_PAYMENT @PACCOUNTID INT, @PAMOUNT MONEY AS
BEGIN
BEGIN TRY

IF NOT EXISTS (SELECT * FROM CLIENTACCOUNT6925 WHERE ACCOUNTID = @PACCOUNTID) THROW 53004, 'ACCOUNT DOES NOT EXITS', 1
IF (@PAMOUNT < 0) THROW 53004, 'ACCOUNT PAYMENT MUST BE POSITIVE',1

DECLARE @CURRENT MONEY; SELECT @CURRENT = BALANCE FROM CLIENTACCOUNT6925 WHERE ACCOUNTID = @PACCOUNTID
DECLARE @currentcash MONEY SELECT @currentcash = AMOUNT FROM GENERALLEDGER6925 WHERE DESCRIPTION = 'ASSETSCASH'
DECLARE @currentstock MONEY SELECT @currentstock = AMOUNT FROM GENERALLEDGER6925 WHERE DESCRIPTION = 'ASSETSSTOCK'
DECLARE @currentaccount MONEY SELECT @currentaccount = AMOUNT FROM GENERALLEDGER6925 WHERE DESCRIPTION = 'ASSETSACCOUNT'
DECLARE @total MONEY = @CURRENT + @PAMOUNT

INSERT INTO ACCOUNTPAYMENT6925(ACCOUNTID,DATETIMERECEIVED,AMOUNT) VALUES (@PACCOUNTID,GETDATE(),@total)

UPDATE CLIENTACCOUNT6925 SET BALANCE = @total WHERE ACCOUNTID = @PACCOUNTID
UPDATE GENERALLEDGER6925 SET AMOUNT = @currentcash + @total WHERE [DESCRIPTION] = 'ASSETSCASH' -- keep in mind, backwards here
UPDATE GENERALLEDGER6925 SET AMOUNT = @currentcash - @total WHERE [DESCRIPTION] = 'ASSETSSTOCK'

END TRY
BEGIN CATCH
    DECLARE @ERRORMESSAGE NVARCHAR(MAX)= ERROR_MESSAGE();

    DECLARE @ERRORNUM INT= ERROR_NUMBER();

    IF ERROR_NUMBER() > 50000
    THROW @ERRORNUM, @ERRORMESSAGE, 1
    ELSE
    THROW 50000, @ERRORMESSAGE, 1
END CATCH;
END
GO

SELECT * FROM CLIENTACCOUNT6925
EXEC MAKE_ACCOUNT_PAYMENT 30002, 100
SELECT * FROM CLIENTACCOUNT6925
SELECT * FROM ACCOUNTPAYMENT6925
GO
IF OBJECT_ID('GET_CLIENT_ACCOUNT_BY_ID') IS NOT NULL
DROP PROCEDURE GET_CLIENT_ACCOUNT_BY_ID;

GO

/*
CREATE PROCEDURE GET_CLIENT_ACCOUNT_BY_ID @PACCOUNTID INT AS
BEGIN

    -- return the specified CLIENT ACCOUNT INCLUDING AND ALL AUTHORISED PERSONS DETAILS

    -- EXCEPTIONS
     -- ACCOUNT DOESNT EXIST THROW ERROR 53002 : ACCOUNT DOES NOT EXIST 
    -- for any other errors throw error : number 50000  message:  error_message()
END;
*/

CREATE PROCEDURE GET_CLIENT_ACCOUNT_BY_ID @PACCOUNTID INT AS
BEGIN

IF NOT EXISTS (SELECT * FROM CLIENTACCOUNT6925 WHERE CLIENTACCOUNT6925.ACCOUNTID = @PACCOUNTID)
THROW 50000, 'ACCOUNT DOES NOT EXIST', 1

BEGIN TRY
SELECT CLIENTACCOUNT6925.ACCOUNTID, CLIENTACCOUNT6925.ACCTNAME, CLIENTACCOUNT6925.BALANCE, CLIENTACCOUNT6925.CREDITLIMIT, AUTHORISEDPERSON6925.USERID, AUTHORISEDPERSON6925.FIRSTNAME, AUTHORISEDPERSON6925.EMAIL, AUTHORISEDPERSON6925.[PASSWORD] FROM CLIENTACCOUNT6925 INNER JOIN AUTHORISEDPERSON6925 ON CLIENTACCOUNT6925.ACCOUNTID = AUTHORISEDPERSON6925.ACCOUNTID WHERE CLIENTACCOUNT6925.ACCOUNTID = @PACCOUNTID;
END TRY
BEGIN CATCH
    DECLARE @ERRORMESSAGE NVARCHAR(MAX)= ERROR_MESSAGE();

    DECLARE @ERRORNUM INT= ERROR_NUMBER();

    IF ERROR_NUMBER() > 50000
    THROW @ERRORNUM, @ERRORMESSAGE, 1
    ELSE
    THROW 50000, @ERRORMESSAGE, 1
END CATCH

END
GO

exec GET_CLIENT_ACCOUNT_BY_ID 30001
GO


IF OBJECT_ID('CREATE_ORDER') IS NOT NULL
DROP PROCEDURE CREATE_ORDER;
GO

/*
CREATE PROCEDURE CREATE_ORDER  @PSHIPPINGADDRESS NVARCHAR(200), @PUSERID INT AS
BEGIN

    -- insert the specified values into the table ORDER6925
    -- SET THE TOTAL TO 0
    -- RETURN THE NEW ORDERS ORDERID

    -- EXCEPTIONS
    -- USER DOES NOT EXIST : THROW ERROR 55002 : USER DOES NOT EXIST
    -- for any other errors throw error : number 50000  message:  error_message()
END;
*/

CREATE PROCEDURE CREATE_ORDER  @PSHIPPINGADDRESS NVARCHAR(200), @PUSERID INT AS
BEGIN
BEGIN TRY

IF NOT EXISTS (SELECT * FROM AUTHORISEDPERSON6925 WHERE USERID = @PUSERID)
THROW 55002, 'USER DOES NOT EXIST', 1

INSERT INTO ORDER6925(SHIPPINGADDRESS,USERID, TOTAL, DATETIMECREATED) VALUES (@PSHIPPINGADDRESS,@PUSERID,0, GETDATE())
SELECT @@IDENTITY;
END TRY
BEGIN CATCH
    DECLARE @ERRORMESSAGE NVARCHAR(MAX)= ERROR_MESSAGE();

    DECLARE @ERRORNUM INT= ERROR_NUMBER();

    IF ERROR_NUMBER() > 50000
    THROW @ERRORNUM, @ERRORMESSAGE, 1
    ELSE
    THROW 50000, @ERRORMESSAGE, 1
END CATCH;
END
GO

EXEC CREATE_ORDER 'SAMPLE TEXT 3',50002
SELECT * FROM ORDER6925
GO


IF OBJECT_ID('GET_ORDER_BY_ID') IS NOT NULL
DROP PROCEDURE GET_ORDER_BY_ID;

GO

/*
CREATE PROCEDURE GET_ORDER_BY_ID @PORDERID INT AS
BEGIN
    -- return the specified ORDER INCLUDING ALL RELATED ORDERLINES

    -- EXCEPTIONS
     -- ORDER DOES NOT EXIST THROW ERROR 54002 : ORDER DOES NOT EXIST 
    -- for any other errors throw error : number 50000  message:  error_message()
END;
*/
CREATE PROCEDURE GET_ORDER_BY_ID @PORDERID INT AS
BEGIN

IF NOT EXISTS (SELECT * FROM ORDER6925 WHERE ORDERID = @PORDERID)
THROW 50000, 'ORDER DOES NOT EXIST', 1

BEGIN TRY
SELECT * FROM ORDER6925 INNER JOIN ORDERLINE6925 ON ORDERLINE6925.ORDERID = ORDER6925.ORDERID WHERE ORDER6925.ORDERID = @PORDERID;
END TRY
BEGIN CATCH
    DECLARE @ERRORMESSAGE NVARCHAR(MAX)= ERROR_MESSAGE();

    DECLARE @ERRORNUM INT= ERROR_NUMBER();

    THROW 50000, @ERRORMESSAGE, 1
END CATCH

END
GO

EXEC GET_ORDER_BY_ID 70001
GO

IF OBJECT_ID('ADD_PRODUCT_TO_ORDER') IS NOT NULL
DROP PROCEDURE ADD_PRODUCT_TO_ORDER;
GO

/*
CREATE PROCEDURE ADD_PRODUCT_TO_ORDER @PORDERID INT, @PPRODIID INT, @PQTY INT, @DISCOUNT DECIMAL AS
BEGIN

    -- THE FOLLOWING MUST BE COMPLETED AS A SINGLE TRANSACTION
    -- CHECK IF THE ORDER HAS ALREADY BEEN FULFILLED (HAS A DATETIMEDISATHCED VALUE)
    -- IF IT HAS BEEN FULFULLILLED GENERATE AN ERROR - SEE EXCEPTIONS SECTION
    -- IF IT HAS NOT BEEN FULLFILLED THEN
    -- IF THE PRODUCT HAS NOT ALREADY BEEN ADDED TO THAT ORDER (I.E. PK IS UNIQUE)
        -- insert the specified values into the table ORDERLINE6925
        -- CALCULATE THE SUBTOTAL VALUE BASED ON THE PRODUCTS SELLPRICE, THE QUANTITY AND THE DISCOUNT
        -- UPDATE THE ORDERS TOTAL - INCREASE IT BY THE VALUE OF THE ORDRLINES SUBTOTAL
    -- ELSE -- the product is aleady in that order 
        -- update the relevant orderline by adding the new quantity to the previous quantity,
        -- RE CALCULATE THE SUBTOTAL VALUE BASED ON THE PRODUCTS SELLPRICE, THE QUANTITY AND THE DISCOUNT
        -- UPDATE THE ORDERS TOTAL - INCREASE IT BY THE VALUE OF THE QTY ADDED TO THE ORDERLINE

    -- EXCEPTIONS
    -- ORDER DOES NOT EXIST THROW ERROR 54002 : ORDER DOES NOT EXIST 
    -- ORDER HAS ALREADY BEEN FULFILLED THROW ERROR 54002 : ORDER HAS ALRADY BEEN FULLFILLED
    -- PRODUCT DOES NOT EXIST THROW ERROR 52002 : PRODUCT DOES NOT EXIST
    -- DISCOUNT IS OUT OF PERMITTED RANGE (SEE TABLE CONSTRAINTS) THROW ERROR 54004 : DISCOUNT OUT OF RANGE
    -- for any other errors throw error : number 50000  message:  error_message()
END;
*/

CREATE PROCEDURE ADD_PRODUCT_TO_ORDER @PORDERID INT, @PPRODIID INT, @PQTY INT, @DISCOUNT DECIMAL AS
BEGIN

BEGIN TRY
IF NOT EXISTS (SELECT * FROM PRODUCT6925 WHERE PRODUCTID = @PPRODIID) THROW 52002, 'PRODUCT DOES NOT EXIST', 1
IF NOT EXISTS (SELECT * FROM ORDER6925 WHERE ORDERID = @PORDERID) THROW 54002, 'ORDER DOES NOT EXIST', 1
IF NOT (@DISCOUNT >= 0 AND @DISCOUNT <= 0.25) THROW 54004, 'DISCOUNT OUT OF RANGE', 1
DECLARE @CURRENTDISPATCH DATETIME
SELECT @CURRENTDISPATCH = DATETIMEDISPATCHED FROM ORDER6925 WHERE ORDERID = @PORDERID
IF @CURRENTDISPATCH IS NOT NULL THROW 54003, 'ORDER HAS ALREADY BEEN FULLFILLED', 1 -- error code above is incorrect
IF NOT EXISTS (SELECT * FROM ORDERLINE6925 WHERE ORDERID = @PORDERID) BEGIN
    DECLARE @CURRENTPRODUCT MONEY
    SELECT @CURRENTPRODUCT = SELLPRICE FROM PRODUCT6925 WHERE PRODUCTID = @PPRODIID
    DECLARE @SUBTOTAL MONEY = (@CURRENTPRODUCT * @PQTY) - @DISCOUNT
    INSERT INTO ORDERLINE6925(ORDERID,PRODUCTID,QUANTITY,DISCOUNT,SUBTOTAL) VALUES(@PORDERID,@PPRODIID,@PQTY,@DISCOUNT,@SUBTOTAL)
    -- DECLARE @CURRENTTOTAL MONEY
    -- SELECT @CURRENTTOTAL = TOTAL FROM ORDER6925 WHERE ORDERID = @PORDERID
    UPDATE ORDER6925 SET TOTAL = TOTAL + @SUBTOTAL WHERE ORDERID = @PORDERID
END
ELSE BEGIN
    DECLARE @CQTY INT
    SELECT @CQTY = QUANTITY FROM ORDERLINE6925 WHERE ORDERID = @PORDERID
    DECLARE @NQTY INT = @CQTY + @PQTY
    DECLARE @CMNY MONEY
    SELECT @CMNY = SELLPRICE FROM PRODUCT6925 WHERE PRODUCTID = @PPRODIID
    DECLARE @SUB MONEY = (@NQTY * @CMNY) - @DISCOUNT
    UPDATE ORDERLINE6925 SET QUANTITY = @NQTY, DISCOUNT = @DISCOUNT, SUBTOTAL = @SUB WHERE ORDERID = @PORDERID
    UPDATE ORDER6925 SET TOTAL = TOTAL + @SUB WHERE ORDERID = @PORDERID

END
END TRY
BEGIN CATCH
    DECLARE @ERRORMESSAGE NVARCHAR(MAX)= ERROR_MESSAGE();

    DECLARE @ERRORNUM INT= ERROR_NUMBER();

    IF ERROR_NUMBER() > 50000
    THROW @ERRORNUM, @ERRORMESSAGE, 1
    ELSE
    THROW 50000, @ERRORMESSAGE, 1
END CATCH;
END
GO

EXEC ADD_PRODUCT_TO_ORDER 70002, 10001, 1,0.10
SELECT * FROM ORDERLINE6925
GO


IF OBJECT_ID('REMOVE_PRODUCT_FROM_ORDER') IS NOT NULL
DROP PROCEDURE REMOVE_PRODUCT_FROM_ORDER;
GO

/*
CREATE PROCEDURE REMOVE_PRODUCT_FROM_ORDER @PORDERID INT, @PPRODIID INT AS
BEGIN

    -- THE FOLLOWING MUST BE COMPLETED AS A SINGLE TRANSACTION
    -- CHECK IF THE ORDER HAS ALREADY BEEN FULFILLED (HAS A DATETIMEDISATHCED VALUE)
    -- IF IT HAS BEEN FULFULLILLED GENERATE AN ERROR - SEE EXCEPTIONS SECTION
    -- IF IT HAS NOT BEEN FULLFILLED THEN
    -- UPDATE THE ORDERS TOTAL - DECREASE IT BY THE VALUE OF THE ORDRLINES SUBTOTAL
    -- DELETE THE RELEVANT ROW FROM ORDERLINE6925

    -- EXCEPTIONS
    -- ORDER DOES NOT EXIST THROW ERROR 54002 : ORDER DOES NOT EXIST 
    -- ORDER HAS ALREADY BEEN FULFILLED THROW ERROR 54002 : ORDER HAS ALREADY BEEN FULLFILLED
    -- PRODUCT DOES NOT EXIST THROW ERROR 52002 : PRODUCT DOES NOT EXIST
    -- PRODUCT HAS NOT BEEN ADDED TO ORDER THROW ERROR 54005 : PRODUCT NOT ON ORDER
    -- for any other errors throw error : number 50000  message:  error_message()
END;
*/
CREATE PROCEDURE REMOVE_PRODUCT_FROM_ORDER @PORDERID INT, @PPRODIID INT AS
BEGIN

BEGIN TRY
IF NOT EXISTS (SELECT * FROM ORDER6925 WHERE ORDERID = @PORDERID) THROW 54002, 'ORDER DOES NOT EXIST', 1
IF NOT EXISTS (SELECT * FROM PRODUCT6925 WHERE PRODUCTID = @PPRODIID) THROW 52002, 'PRODUCT DOES NOT EXIST', 1
IF NOT EXISTS (SELECT * FROM ORDERLINE6925 WHERE (ORDERID = @PORDERID AND PRODUCTID = @PPRODIID)) THROW 54005, 'PRODUCT NOT ON ORDER', 1
DECLARE @CURRENTDISPATCH DATETIME
SELECT @CURRENTDISPATCH = DATETIMEDISPATCHED FROM ORDER6925 WHERE ORDERID = @PORDERID
IF @CURRENTDISPATCH IS NOT NULL THROW 54003, 'ORDER HAS ALREADY BEEN FULLFILLED', 1

DECLARE @CTOTAL MONEY
SELECT @CTOTAL = SUBTOTAL FROM ORDERLINE6925 WHERE ORDERID = @PORDERID
UPDATE ORDER6925 SET TOTAL = TOTAL - @CTOTAL WHERE ORDERID = @PORDERID
DELETE FROM ORDERLINE6925 WHERE ORDERID = @PORDERID
END TRY
BEGIN CATCH
    DECLARE @ERRORMESSAGE NVARCHAR(MAX)= ERROR_MESSAGE();

    DECLARE @ERRORNUM INT= ERROR_NUMBER();

    IF ERROR_NUMBER() > 50000
    THROW @ERRORNUM, @ERRORMESSAGE, 1
    ELSE
    THROW 50000, @ERRORMESSAGE, 1
END CATCH;

END
GO

PRINT 'WARNING: RUNNING THIS AND FULFILLING ORDER IS MUTUALLY EXCLUSIVE BECAUSE NO SUCH PRODUCT EXISTS'
--EXEC REMOVE_PRODUCT_FROM_ORDER 70002, 10001
SELECT * FROM ORDERLINE6925
GO


IF OBJECT_ID('GET_OPEN_ORDERS') IS NOT NULL
DROP PROCEDURE GET_OPEN_ORDERS;
GO

/*
CREATE PROCEDURE GET_OPEN_ORDERS AS
BEGIN

    -- RETURN A CURSOR WHICH REFERENCES ALL CURRENTLY OPEN (NOT FULFILLED) ORDERS

    -- EXCEPTIONS
    -- for any other errors throw error : number 50000  message:  error_message()
END;
*/CREATE PROCEDURE GET_OPEN_ORDERS AS
BEGIN
BEGIN TRY
    DECLARE CURS CURSOR FOR SELECT ORDERID FROM ORDER6925 WHERE DATETIMEDISPATCHED IS NOT NULL
    OPEN CURS
    END TRY
BEGIN CATCH
    DECLARE @ERRORMESSAGE NVARCHAR(MAX)= ERROR_MESSAGE();

    DECLARE @ERRORNUM INT= ERROR_NUMBER();

    THROW 50000, @ERRORMESSAGE, 1
END CATCH
END
GO

EXEC GET_OPEN_ORDERS
GO


IF OBJECT_ID('FULLFILL_ORDER') IS NOT NULL
DROP PROCEDURE FULLFILL_ORDER;

GO

/*
CREATE PROCEDURE FULLFILL_ORDER @PORDERID INT AS
BEGIN

    -- THE FOLLOWING MUST BE COMPLETED AS A SINGLE TRANSACTION

    -- CHECK IF THE ORDER HAS ALREADY BEEN FULFILLED (HAS A DATETIMEDISATHCED VALUE)
    -- IF IT HAS BEEN FULFULLILLED GENERATE AN ERROR - SEE EXCEPTIONS SECTION
    -- IF IT HAS NOT BEEN FULLFILLED THEN

    -- UPDATE THE ORDERS DATETIMEDISPATCHED WITH THE CURRENT DATE TIME
    -- ** TRICKY** FOR EACH PRODUCT IN THE ORDER FIND INVENTORY WHICH HAS SUFFICIENT UNITS OF THE PRODUCT IN STOCK 
            -- AND DECREASE THE INVENTORY BY THE AMOUNT OF THE PRODUCT IN TH ORDER
    -- INCREASE THE RELEVANT CLIENTACCOUNTS BALANCE BY THE TOTAL VALUE OF THE ORDER
    -- INCREASE THE GENERAL LEDGER ACCOUNT ASSETS AMOUNT BY THE TOTAL VALUE OF THE ORDER
    -- ** TRICKY** DECREASE THE GENERAL LEDGER STOCK ASSESTS AMOUNT BY THE WHOLESALE (QTY * BUYPRICE) OF ALL THE PRODUCTS IN THE ORDER

    -- EXCEPTIONS
    -- INSUFFICIENT INVENTORY OF ONE OR MORE PRODUCTS TO FULFILL ORDER THROW ERROR 54006: INSUFFUCIENT INVENTORY TO FULFILL
    -- ORDER HAS ALREADY BEEN FULFILLED THROW ERROR 54002 : ORDER HAS ALREADY BEEN FULLFILLED
    -- ORDER DOES NOT EXIST THROW ERROR 54002 : ORDER DOES NOT EXIST 
    -- for any other errors throw error : number 50000  message:  error_message()
END;
*/

CREATE PROCEDURE FULLFILL_ORDER @PORDERID INT AS
BEGIN
BEGIN TRY
    IF NOT EXISTS (SELECT * FROM ORDER6925 WHERE ORDERID = @PORDERID) THROW 54002, 'ORDER DOES NOT EXIST', 1
    DECLARE @CURRENTDISPATCH DATE
    SELECT @CURRENTDISPATCH = DATETIMEDISPATCHED FROM ORDER6925 WHERE ORDERID = @PORDERID
    IF @CURRENTDISPATCH IS NOT NULL THROW 54003, 'ORDER HAS ALREADY BEEN FULLFILLED', 1

    DECLARE @LOCWITHSTOCK NVARCHAR(8);
PRINT @PORDERID
    DECLARE CURS3 CURSOR FOR SELECT PRODUCTID, QUANTITY FROM ORDERLINE6925 WHERE ORDERID = @PORDERID;
    DECLARE @PRODID INT;
    DECLARE @DONE1 INT = 0;
    DECLARE @QTY INT;
    OPEN CURS3 FETCH NEXT FROM CURS3 INTO @PRODID, @QTY;
    DECLARE @FETCH_OUTER INT = @@FETCH_STATUS;

    WHILE @FETCH_OUTER = 0 and @DONE1 = 0
    BEGIN

        DECLARE CURS2 CURSOR FOR SELECT LOCATIONID, NUMINSTOCK FROM INVENTORY6925 WHERE PRODUCTID = @PRODID
        DECLARE @LOC NVARCHAR(8);
        DECLARE @STK INT;
        DECLARE @DONE2 INT = 0;
        OPEN CURS2 FETCH NEXT FROM CURS2 INTO @LOC, @STK;
        DECLARE @FETCH_INNER INT = @@FETCH_STATUS;
        WHILE @FETCH_INNER = 0 and @DONE2 = 0
        BEGIN
            IF (@QTY <= @STK)
            BEGIN
                SET @LOCWITHSTOCK = @LOC
                SET @DONE1 = 1
                SET @DONE2 = 1
                UPDATE INVENTORY6925 SET NUMINSTOCK = @STK - @QTY WHERE LOCATIONID = @LOC AND PRODUCTID = @PRODID
            END

    
        END
        CLOSE CURS2
        DEALLOCATE CURS2
    
    END
    CLOSE CURS3
    DEALLOCATE CURS3
    IF @LOCWITHSTOCK IS NULL THROW 54006, 'INSUFFICIENT INVENTORY TO FULFILL!!', 1; -- null = not assigned
    
    DECLARE @USID INT;
    DECLARE @ACID INT;
    DECLARE @ORD MONEY;
    SELECT @USID = USERID FROM ORDER6925 WHERE ORDERID = @PORDERID;
    SELECT @ORD = TOTAL FROM ORDER6925 WHERE ORDERID = @PORDERID;
    DECLARE @BP MONEY;
    SELECT @BP = BUYPRICE FROM PRODUCT6925 WHERE PRODUCTID = @PRODID;
    SELECT @ACID = ACCOUNTID FROM AUTHORISEDPERSON6925 WHERE USERID = @USID;

    UPDATE ORDER6925 SET DATETIMEDISPATCHED = GETDATE() WHERE ORDERID = @PORDERID
    UPDATE CLIENTACCOUNT6925 SET BALANCE = BALANCE + @ORD WHERE ACCOUNTID = @ACID
    UPDATE GENERALLEDGER6925 SET AMOUNT = AMOUNT + @ORD WHERE [DESCRIPTION] = 'ASSETSCASH'
    UPDATE GENERALLEDGER6925 SET AMOUNT = AMOUNT - (@BP*@QTY) WHERE [DESCRIPTION] = 'ASSETSTOCK'
    SELECT * FROM ORDER6925 WHERE ORDERID = @PORDERID;
END TRY
BEGIN CATCH
    DECLARE @ERRORMESSAGE NVARCHAR(MAX)= ERROR_MESSAGE();

    DECLARE @ERRORNUM INT= ERROR_NUMBER();

    IF ERROR_NUMBER() > 50000
    THROW @ERRORNUM, @ERRORMESSAGE, 1
    ELSE
    THROW 50000, @ERRORMESSAGE, 1
END CATCH;
END;

GO
PRINT 'WARNING: RUNNING THIS AND DELETING PRODUCTS MUTUALLY EXCLUSIVE BECAUSE NO SUCH PRODUCT EXISTS'

SELECT * FROM ORDER6925
SELECT * FROM CLIENTACCOUNT6925
SELECT * FROM GENERALLEDGER6925
--EXEC FULLFILL_ORDER 70002
SELECT * FROM ORDER6925
SELECT * FROM CLIENTACCOUNT6925
SELECT * FROM GENERALLEDGER6925

GO















--###############################################################
-- BELLOW CODE IS AN EXAMPLE ONLY - IT IS NOT PART OF THE TASK
--###############################################################
/*

CREATE PROCEDURE ADD_LOCATION @PCUSTID INT, @PCUSTNAME NVARCHAR(100) AS
BEGIN    
    BEGIN TRY        
        IF @PCUSTID < 1 OR @PCUSTID > 499            
        THROW 50020, 'Customer ID out of range', 1        
        INSERT INTO CUSTOMER (CUSTID, CUSTNAME, SALES_YTD, STATUS)         
        VALUES (@PCUSTID, @PCUSTNAME, 0, 'OK');    
    END TRY    
    BEGIN CATCH        
        if ERROR_NUMBER() = 2627            
            THROW 50010, 'Duplicate customer ID', 1        
        ELSE IF ERROR_NUMBER() = 50020            
            THROW        
        ELSE            
            BEGIN                
                DECLARE @ERRORMESSAGE NVARCHAR(MAX) = ERROR_MESSAGE();                
                THROW 50000, @ERRORMESSAGE, 1            
            END;    
    END CATCH;
END;

GO

EXEC ADD_CUSTOMER @pcustid = 1, @pcustname = 'testdude2';
EXEC ADD_CUSTOMER @pcustid = 500, @pcustname = 'testdude3';select * from customer;

*/