ADD.sql

SELECT * FROM CORDER order by O_IDX desc;

SELECT * FROM CUSTOMER_SEND;

DROP IF EXISTS PRICE_RATIO;
CREATE TABLE PRICE_RATIO (
	SERVICE_CUSTOMER VARCHAR(8) PRIMARY KEY,
	RATIO REAL
)

INSERT INTO PRICE_RATIO (SERVICE_CUSTOMER,RATIO) VALUES
	('NORMAL',1.0),
	('DISCOUNT',0.8),
	('EXPRESS',1.2)

DROP IF EXISTS KILOMETER_DISTANCE;
CREATE TABLE KILOMETER_DISTANCE (
	PROVINCE VARCHAR(32) PRIMARY KEY,
	DISTANCE INT
)

INSERT INTO KILOMETER_DISTANCE (PROVINCE,DISTANCE) VALUES
	('HA NOI',0),
	('HO CHI MINH',1710),
	('HUE',658),
	('DA NANG',763),
	('BAC NINH',31),
	('AN GIANG',1900),
	('DA LAT',1481),
	('THANH HOA',153),
	('QUANG TRI',889),
	('PHU THO',93),
	('NHA TRANG',1278),
	('CAN THO',1877),
	('LAO CAI',338);
GO

SELECT * FROM CORDER
GO

--tinh toan tong so tien gui cua nguoi gui
--tham so dau vao: @ssn nguoi gui -> tra ra tong so tien nguoi do phai tra tu cac hoa don

DROP TABLE IF EXISTS ORDER_ASC
SELECT TOP 20 * INTO ORDER_ASC FROM CORDER ORDER BY O_IDX
GO

CREATE FUNCTION GET_RATIO_FROM_SSN(@ssn VARCHAR(10))
RETURNS REAL
AS
BEGIN
	DECLARE @ratio REAL
	SET @ratio = (SELECT TEMP.RATIO FROM
					(SELECT CUSTOMER_SEND.SSN, PRICE_RATIO.RATIO
					FROM CUSTOMER_SEND
					JOIN PRICE_RATIO ON CUSTOMER_SEND.SERVICE = PRICE_RATIO.SERVICE_CUSTOMER) 
				AS TEMP WHERE TEMP.SSN = @ssn)
	RETURN @ratio;
END;
GO

CREATE FUNCTION COST_FOR_CUSTOMER_SEND(
	@ssn VARCHAR(10)
)
RETURNS REAL
AS
BEGIN
	--CHECK SSN
	IF LEN(@ssn) <> 9 OR @ssn NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
		RETURN -1.0
	ELSE IF @ssn NOT IN (SELECT SSN FROM PERSON) 
		RETURN -1.0
	--QUERY
	--1-N CUSTOMER SEND -> RETRIEVE SERVICE_RATIO;
	DECLARE @ratio REAL
	SET @ratio = (SELECT dbo.GET_RATIO_FROM_SSN(@ssn))
	--1-N CUSTOMER RECEIVE -> RETRIVE ADDRESS_RECEIVE;
	DECLARE @cost REAL = 0.0;
	DECLARE @cnt INT = 1;

	DECLARE @o_id VARCHAR(10) = ''; --DETERMINE PACKAGE ID
	DECLARE @ssn_cr VARCHAR(9) = '';
	DECLARE @add_cr VARCHAR(64) = '';
	DECLARE @cost_cnt REAL = 0.0;

	WHILE 0=0
	BEGIN
		IF @cnt in (SELECT O_IDX FROM CORDER) 
		BEGIN
			SET @o_id = (SELECT ORDERID FROM CORDER WHERE O_IDX = @cnt);
			SET @ssn_cr = (SELECT SSN_CR FROM CORDER WHERE O_IDX = @cnt);
			--1-N CUSTOMER_RECEIVE -> RETRIEVE ADDRESS_RECEIVE
			SET @add_cr = (SELECT ADDRESS FROM CUSTOMER_RECEIVE WHERE SSN = @ssn_cr)
			--GET DISTANCE FROM ADRRESS RECEIVE
			SET @cost_cnt = (SELECT dbo.COST_ORDER(@add_cr,@o_id)) 
			SET @cost = @cost + @cost_cnt 

			SET @cnt = @cnt + 1;
		END
		ELSE
			BREAK
	END;

	
	RETURN @cost * @ratio
END;
GO

CREATE FUNCTION COST_ORDER (@add_cr VARCHAR(64), @o_id VARCHAR(11))
RETURNS VARCHAR(64)
BEGIN
	IF LEN(@o_id) <> 10 OR @o_id NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
		RETURN -1.0;
	IF @o_id NOT IN (SELECT ORDERID FROM CORDER)
		RETURN -1.0;
	IF @add_cr NOT IN (SELECT PROVINCE FROM KILOMETER_DISTANCE)
		RETURN -1.0;
	DECLARE @distance_cr INT = 0;
	SET @distance_cr = (SELECT DISTANCE FROM KILOMETER_DISTANCE WHERE PROVINCE = @add_cr);
	DECLARE @cost REAL = 0.0;
	DECLARE @cnt INT = 1;
	DECLARE @oid VARCHAR(10) = '';
	DECLARE @location VARCHAR(64) = '';
	DECLARE @distance_cs INT = '';
	DECLARE @weight FLOAT = 0.0;
	WHILE 0 = 0
	BEGIN
		IF @cnt in (SELECT P_IDX FROM PACKAGE) 
		BEGIN
			--get o_id to check @o_id
			SET @oid = (SELECT ORDERID FROM PACKAGE WHERE P_IDX = @cnt)
			IF @o_id <> @oid
				--get location
				SET @cnt = @cnt + 1;
			ELSE
				BEGIN
					SET @location = (SELECT LOCATION FROM PACKAGE WHERE ORDERID = @o_id AND P_IDX = @cnt)
					SET @distance_cs = (SELECT DISTANCE FROM KILOMETER_DISTANCE WHERE PROVINCE = @location);
					SET @weight = (SELECT PWEIGHT FROM PACKAGE WHERE P_IDX = @cnt AND ORDERID = @oid)
				
					SET @distance_cs = ABS(@distance_cr - @distance_cs) * @weight
					SET @cost = @cost + @distance_cs
					SET @cnt = @cnt + 1;
				END
		END
		ELSE
			BREAK
		
	END;

	RETURN @cost;
END
GO


SELECT * FROM ORDER_ASC
SELECT * FROM CUSTOMER_RECEIVE
SELECT * FROM PACKAGE

SELECT dbo.COST_ORDER('CAN THO','3403032743')

SELECT dbo.COST_FOR_CUSTOMER_SEND('182835988') * 50

SELECT * FROM PACKAGE
SELECT P_IDX FROM PACKAGE