RetailDB.sql

USE MASTER;
GO

DROP DATABASE IF EXISTS RetailDB;
GO

CREATE DATABASE RetailDB;
GO

Use RetailDB;
GO

CREATE TABLE R_RETAILSTORE(
RS_ID INT PRIMARY KEY IDENTITY(1,1),
RS_StoreName VARCHAR(30) NOT NULL,
RS_ADDRESSLineOne VARCHAR(100) NOT NULL,
RS_ADDRESSLineTwo VARCHAR(100) ,
RS_ADDRESSCity VARCHAR(100) NOT NULL,
RS_ADDRESSState VARCHAR(100) NOT NULL,
RS_ADDRESSCountry VARCHAR(100) NOT NULL,
RS_ADDRESSZipCode VARCHAR(100) NOT NULL,
);
GO

CREATE TABLE R_Users
(
USR_ID INT IDENTITY(1,1) PRIMARY KEY,
USR_NAME TEXT,
USR_USERNAME VARCHAR(30) UNIQUE,
USR_PASSWORD VARCHAR(30) NOT NULL,
USR_EMAIL VARCHAR(30) UNIQUE,
USR_ISDISABLED INT CHECK(USR_ISDISABLED=0 OR USR_ISDISABLED=1) DEFAULT 1
);
GO


CREATE TABLE R_SUPPLIERS
(
S_ID INT PRIMARY KEY IDENTITY(11,1),
S_NAME VARCHAR(50) UNIQUE,
S_MOBILE VARCHAR(10) UNIQUE,
S_EMAILID VARCHAR(50) UNIQUE,
S_ADDRESS VARCHAR(50),
S_STATUS INT CHECK(S_STATUS=0 OR S_STATUS=1) DEFAULT 1
);
GO


CREATE TABLE R_CREATECATEGORY
(
CC_CATEGORYCODE INT IDENTITY(101,1) PRIMARY KEY,
CC_CATEGORY VARCHAR(50)  NOT NULL,
CC_SUBCATEGORY VARCHAR(50) NOT NULL UNIQUE,
CC_STATUS INT CHECK(CC_STATUS=0 OR CC_STATUS=1) DEFAULT 1
);
GO


CREATE TABLE R_CREATEITEM
(
CI_ITEMCODE INT IDENTITY(101,1) PRIMARY KEY,
CI_ITEMCATEGORY VARCHAR(50),
CI_ITEMSUBCAT VARCHAR(50),
CI_SUPPLIERNAME VARCHAR(50),
CI_ITEMNAME VARCHAR(100) UNIQUE,
CI_STOCKINHAND INT NOT NULL,
CI_PURCHASEPRICEUNIT INT NOT NULL,
CI_SALESPRICEUNIT INT NOT NULL,
CI_STATUS INT CHECK(CI_STATUS=0 OR CI_STATUS=1) DEFAULT 1
);
GO


CREATE Table R_PURCHASERECORD
(
PU_INVOICENUMBER INT PRIMARY KEY,
PU_PURCHASEDATE DATE DEFAULT GETDATE() ,
PU_TOTALPURCHASEVALUE INT ,
PU_SUPPLIERNAME VARCHAR(50) 
);
GO


CREATE TABLE R_PURCHASEINVOICE
(
PI_INVOICENUMBER INT,
PI_PURCHASEQUANTITY INT,
PI_TOTALPURCHASEVALUE INT,
PI_ITEMNAME VARCHAR(50),
PI_PURCHASEPRICEUNIT INT,
PI_ITEMCODE INT
);
GO

CREATE Table R_SALESRECORD
(
SU_INVOICENUMBER INT PRIMARY KEY ,
SU_SALESDATE DATE DEFAULT GETDATE(),
SU_TOTALSALESVALUE INT,
SU_CUSTOMERNAME VARCHAR(50),
SU_CUSTOMERNUMBER VARCHAR(10) UNIQUE
);
GO

CREATE TABLE R_SALESINVOICE
(
SI_INVOICENUMBER INT,
SI_SALESQUANTITY INT,
SI_TOTALSALESVALUE INT,
SI_ITEMNAME VARCHAR(50),
SI_SALESPRICEUNIT INT,
SI_ITEMCODE INT
);
GO




---STORED PROCEDURES--
-------------EXTRA PROCEDURES---------

--GET CREDENTIALS

CREATE OR ALTER PROC [dbo].[GETCREDENTIAL]
AS
SELECT USR_USERNAME,USR_PASSWORD,USR_ISDISABLED,USR_ID FROM R_USERS;
GO

--INACTIVE ADMIN

CREATE OR ALTER PROC INACTIVEUSER
@UId int
AS
UPDATE R_USERS SET USR_ISDISABLED = @UId
WHERE USR_ID=1;
GO


--CREATE PURCHASE RECORD INVOICE NUMBER

CREATE OR ALTER PROC CREATEPURCHASERECORDS
@uPU_TOTALPURCHASEVALUE INT,
@uPU_SUPPLIERNAME VARCHAR(50),
@uPU_INVOICENUMBER INT
AS
INSERT INTO R_PURCHASERECORD(PU_INVOICENUMBER,PU_TOTALPURCHASEVALUE,PU_SUPPLIERNAME)
VALUES(@uPU_INVOICENUMBER,@uPU_TOTALPURCHASEVALUE,@uPU_SUPPLIERNAME);
GO

--CREATE SALES RECORD INVOICE NUMBER

CREATE OR ALTER PROC CREATESALESRECORDS
@uSU_TOTALSALESVALUE INT,
@uSU_CUSTOMERNAME VARCHAR(50),
@uSU_INVOICENUMBER INT,
@uSU_CUSTOMERNUMBER VARCHAR(10)
AS
INSERT INTO R_SALESRECORD(SU_INVOICENUMBER,SU_TOTALSALESVALUE,SU_CUSTOMERNAME,SU_CUSTOMERNUMBER)
VALUES(@uSU_INVOICENUMBER,@uSU_TOTALSALESVALUE,@uSU_CUSTOMERNAME, @uSU_CUSTOMERNUMBER);
GO

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


----------VIEW PROCEDURES------------

--VIEW USERS

CREATE OR ALTER PROC VIEWUSER
AS
SELECT USR_ID,USR_NAME,USR_USERNAME,USR_EMAIL,USR_ISDISABLED FROM R_USERS;
GO

--View Store

CREATE OR ALTER PROC VIEWSTORE
AS
SELECT * FROM R_RETAILSTORE;
GO

--VIEW SUPPLIER
CREATE OR ALTER PROC VIEWSUPPLIER
AS
SELECT * FROM R_SUPPLIERS;
GO


 --VIEW INVENTORY ITEMS
CREATE OR ALTER PROC VIEWINVENTORYITEMS
AS
SELECT * FROM R_CREATEITEM;
GO

--VIEW INVENTORY CATEGORY
CREATE OR ALTER PROC VIEWINVENTORYCATEGORY
AS
SELECT * FROM R_CREATECATEGORY;
GO

--VIEW PURCHASE RECORDS
CREATE OR ALTER PROC VIEWPURCHASERECORDS
AS
SELECT * FROM R_PURCHASERECORD;
GO

--VIEW PURCHASE ITEM LIST

CREATE OR ALTER PROC VIEWPURCHASEITEMLIST
@pINVOICENUMBER INT
AS
SELECT * FROM R_PURCHASEINVOICE WHERE PI_INVOICENUMBER = @pINVOICENUMBER;
GO

--VIEW SALES RECORDS
CREATE OR ALTER PROC VIEWSALESRECORDS
AS
SELECT * FROM R_SALESRECORD;
GO

--VIEW SALES INVOICE
CREATE OR ALTER PROC VIEWSALESITEMLIST
@sINVOICENUMBER INT
AS
SELECT * FROM R_SALESINVOICE WHERE SI_INVOICENUMBER = @sINVOICENUMBER;
GO

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

----------------ADD PROCEDURES------------


--ADD USER

CREATE OR ALTER PROC ADDUSER
@uUSR_NAME TEXT,
@uUSR_USRNAME TEXT,
@uUSR_PASSWORD VARCHAR(30),
@uUSR_EMAIL VARCHAR(30)

AS
INSERT INTO R_USERS(USR_NAME,USR_USERNAME,USR_PASSWORD, USR_EMAIL) VALUES(@uUSR_NAME,@uUSR_USRNAME,@uUSR_PASSWORD,@uUSR_EMAIL);
GO


--ADD SUPPLIER

CREATE OR ALTER PROC ADDSUPPLIER
@sS_NAME VARCHAR(50),
@sS_MOBILE VARCHAR(50),
@sS_EMAIL VARCHAR(50),
@sS_ADDRESS VARCHAR(50)
AS
INSERT INTO R_SUPPLIERS(S_NAME,S_MOBILE,S_EMAILID,S_ADDRESS) VALUES(@sS_NAME,@sS_MOBILE,@sS_EMAIL,@sS_ADDRESS);
GO

--Add Store

CREATE OR ALTER PROC ADDSTORE
@rRS_StoreName VARCHAR(50),
@rRS_ADDRESSLineOne VARCHAR(100),
@rRS_ADDRESSLineTwo VARCHAR(100),
@rRS_ADDRESSCity VARCHAR(100),
@rRS_ADDRESSState VARCHAR(100),
@rRS_ADDRESSCountry VARCHAR(100),
@rRS_ADDRESSZipCode int

AS
INSERT INTO R_RETAILSTORE(RS_StoreName,RS_ADDRESSLineOne,RS_ADDRESSLineTwo,RS_ADDRESSCity,RS_ADDRESSState,RS_ADDRESSCountry,RS_ADDRESSZipCode)
VALUES(@rRS_StoreName,@rRS_ADDRESSLineOne,@rRS_ADDRESSLineTwo,@rRS_ADDRESSCity,@rRS_ADDRESSState,@rRS_ADDRESSCountry,@rRS_ADDRESSZipCode);
GO


--ADD CATEGORY
CREATE OR ALTER PROC ADDCATEGORY
@cc_CATEGORY VARCHAR(50),
@cc_SUBCATEGORY VARCHAR(50)
AS
INSERT INTO R_CREATECATEGORY(CC_CATEGORY,CC_SUBCATEGORY)
VALUES(@cc_CATEGORY,@cc_SUBCATEGORY);
GO

--ADD ITEMS
CREATE OR ALTER PROC ADDNEWITEM
@ci_CATEGORY VARCHAR(50),
@ci_SUBCATEGORY VARCHAR(50),
@ci_ITEMNAME VARCHAR(100),
@ci_SUPPLIERNAME VARCHAR(50),
@ci_PURCHASEPRICEUNIT INT,
@ci_STOCKINHAND INT,
@ci_SALESPRICEUNIT INT
AS
INSERT INTO R_CREATEITEM(CI_ITEMCATEGORY,CI_ITEMSUBCAT,CI_ITEMNAME,
CI_SUPPLIERNAME,CI_PURCHASEPRICEUNIT,CI_STOCKINHAND,CI_SALESPRICEUNIT)
VALUES
(@ci_CATEGORY,@ci_SUBCATEGORY,@ci_ITEMNAME,
@ci_SUPPLIERNAME,@ci_PURCHASEPRICEUNIT,
@ci_STOCKINHAND, @ci_SALESPRICEUNIT);
GO


--ADD PURCHASE ITEMS
CREATE OR ALTER PROC ADDPURCHASEITEM
@pPI_INVOICENUMBER INT,
@pPI_PURCHASEQUANTITY INT,
@pPI_ITEMNAME VARCHAR(50),
@pPI_PURCHASEPRICEPERUNIT INT,
@pPI_ITEMCODE INT
AS
INSERT INTO R_PURCHASEINVOICE(PI_INVOICENUMBER, PI_PURCHASEQUANTITY, PI_TOTALPURCHASEVALUE, PI_ITEMNAME, PI_PURCHASEPRICEUNIT, PI_ITEMCODE)
VALUES(@pPI_INVOICENUMBER, @pPI_PURCHASEQUANTITY, @pPI_PURCHASEQUANTITY*@pPI_PURCHASEPRICEPERUNIT,
@pPI_ITEMNAME, @pPI_PURCHASEPRICEPERUNIT, @pPI_ITEMCODE);
GO

--ADD SALES ITEMS
CREATE OR ALTER PROC ADDSALESITEM
@sSI_INVOICENUMBER INT,
@sSI_SALESQUANTITY INT,
@sSI_ITEMNAME VARCHAR(50),
@sSI_SALESPRICEPERUNIT INT,
@sSI_ITEMCODE INT
AS
INSERT INTO R_SALESINVOICE(SI_INVOICENUMBER, SI_SALESQUANTITY, SI_TOTALSALESVALUE, SI_ITEMNAME, SI_SALESPRICEUNIT, SI_ITEMCODE)
VALUES(@sSI_INVOICENUMBER, @sSI_SALESQUANTITY, @sSI_SALESQUANTITY*@sSI_SALESPRICEPERUNIT,
@sSI_ITEMNAME, @sSI_SALESPRICEPERUNIT, @sSI_ITEMCODE);
GO


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

-------UPDATE PROCEDURES---------

--Update Store

CREATE OR ALTER PROC UPDATESTORE
@rRs_ID int,
@rRS_StoreName VARCHAR(50),
@rRS_ADDRESSLineOne VARCHAR(100),
@rRS_ADDRESSLineTwo VARCHAR(100),
@rRS_ADDRESSCity VARCHAR(100),
@rRS_ADDRESSState VARCHAR(100),
@rRS_ADDRESSCountry VARCHAR(100),
@rRS_ADDRESSZipCode int
AS
UPDATE R_RETAILSTORE set  RS_StoreName=@rRS_StoreName, RS_ADDRESSLineOne= @rRS_ADDRESSLineOne, RS_ADDRESSLineTwo=@rRS_ADDRESSLineTwo,
RS_ADDRESSCity=@rRS_ADDRESSCity, RS_ADDRESSState=@rRS_ADDRESSState,RS_ADDRESSCountry=@rRS_ADDRESSCountry,RS_ADDRESSZipCode=@rRS_ADDRESSZipCode 
where RS_ID= @rRS_ID;
Go


--Update Supplier

CREATE OR ALTER PROC UPDATESUPPLIERS
@sS_NAME VARCHAR(50),
@sS_EMAIL VARCHAR(50),
@sS_ADDRESS VARCHAR(50),
@sS_MOBILE VARCHAR(10),
@sS_STATUS INT,
@sS_ID INT
AS
UPDATE R_SUPPLIERS
SET S_MOBILE =@sS_MOBILE, S_STATUS=@sS_STATUS, S_NAME=@sS_NAME, S_ADDRESS=@sS_ADDRESS, S_EMAILID=@sS_EMAIL
WHERE S_ID =@sS_ID;
GO

--Update User

CREATE OR ALTER PROC UPDATEUSER
@sUSR_NAME TEXT,
@sUSR_USERNAME TEXT,
@sUSR_PASSWORD VARCHAR(50),
@sUSR_EMAIL VARCHAR(50),
@sUSR_ISDISABLED INT,
@sUSR_ID INT
AS
UPDATE R_Users
SET USR_NAME=@sUSR_NAME, USR_USERNAME=@sUSR_USERNAME, USR_PASSWORD=@sUSR_PASSWORD, USR_EMAIL=@sUSR_EMAIL, USR_ISDISABLED=@sUSR_ISDISABLED
WHERE USR_ID=@sUSR_ID;
GO


--UPDATE CATEGORY
CREATE OR ALTER PROC UPDATECATEGORY
@cc_CATEGORY VARCHAR(50),
@cc_SUBCATEGORY VARCHAR(50),
@cc_STATUS INT,
@cc_CATEGORYCODE INT
AS
UPDATE R_CREATECATEGORY SET CC_CATEGORY=@cc_CATEGORY,
CC_SUBCATEGORY=@cc_SUBCATEGORY,
CC_STATUS=@cc_STATUS
WHERE CC_CATEGORYCODE=@cc_CATEGORYCODE;
GO


--UPDATE ITEMS

CREATE OR ALTER PROC UPDATEITEM

@ci_ITEMNAME VARCHAR(100),
@ci_PURCHASEPRICEUNIT INT,
@ci_SUPPLIERNAME VARCHAR(50),
@ci_STOCKINHAND INT,
@ci_SALESPRICEUNIT INT,
@ci_STATUS INT,
@ci_ITEMCODE INT
AS
UPDATE R_CREATEITEM 
SET 
CI_ITEMNAME=@ci_ITEMNAME, CI_PURCHASEPRICEUNIT=@ci_PURCHASEPRICEUNIT,
CI_SUPPLIERNAME=@ci_SUPPLIERNAME,CI_STOCKINHAND=@ci_STOCKINHAND,
CI_SALESPRICEUNIT=@ci_SALESPRICEUNIT,
CI_STATUS=@ci_STATUS
WHERE CI_ITEMCODE=@ci_ITEMCODE;
GO

--UPDATE PURCHASE ITEM

CREATE OR ALTER PROC UPDATEPURCHASEITEM
@pITEMCODE INT,
@pPURCHASEQUANTITY INT
AS
UPDATE R_PURCHASEINVOICE SET PI_PURCHASEQUANTITY=@pPURCHASEQUANTITY
WHERE PI_ITEMCODE=@pITEMCODE;
GO

--UPDATE TOTAL PURCHASE

create or alter proc UpdateTotalInPurchaseRecords
@totalPurchaseValue int,
@invoiceNumber int
as
select @totalPurchaseValue = sum(pi_totalpurchasevalue)  from r_purchaseinvoice where pi_invoicenumber = @invoiceNumber
update R_PURCHASERECORD set pu_totalpurchasevalue=@totalPurchaseValue where pu_invoicenumber = @invoiceNumber;
go


--UPDATE SALES ITEM

CREATE OR ALTER PROC UPDATESALESITEM
@sITEMCODE INT,
@sSALESQUANTITY INT
AS
UPDATE R_SALESINVOICE SET SI_SALESQUANTITY=@sSALESQUANTITY
WHERE SI_ITEMCODE=@sITEMCODE;
GO

--UPDATE TOTAL SALES

create or alter proc UpdateTotalInSalesRecords
@totalSalesValue int,
@invoiceNumber int
as
select @totalSalesValue = sum(si_totalsalesvalue)  from r_salesinvoice where si_invoicenumber = @invoiceNumber
update R_SALESRECORD set su_totalsalesvalue=@totalSalesValue where su_invoicenumber = @invoiceNumber;
go

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

-------DEFAULT VALUES FOR TABLES-----------
--------DEFAULT USERS

INSERT INTO R_USERS(USR_NAME, USR_USERNAME, USR_PASSWORD, USR_EMAIL, USR_ISDISABLED) 
VALUES
('Administrator','admin','admin','admin@retailstore.com',1);
GO