/* 
  https://www.postgresqltutorial.com/postgresql-create-procedure/ 
  從 ms-sql 的 view 改寫為 postgresql, 問題不大, 排除 table 和 fields 的些微差異,
  主要的問題是
  1.字串相加的語法 + -> ||
  2.convert 轉為 momey 取消
  3.JOIN 的語法和我慣用法不同
*/

/****** Object:  View Orders Qry    Script Date: 08/12/2011 11:46:04 ******/
create or replace view orders_qry 
as
select orders.orderid, orders.customerid, orders.employeeid, orders.orderdate, orders.requireddate, 
	orders.shippeddate, orders.shipperid, orders.freight, orders.shipname, orders.shipaddress, orders.shipcity, 
	orders.shipregion, orders.shippostalcode, orders.shipcountry, 
	customers.companyname, customers.address, customers.city, customers.region, customers.postalcode, customers.country
from customers inner join orders on customers.customerid = orders.customerid


/****** Object:  View Quarterly Orders    Script Date: 08/12/2011 11:46:04 ******/
create or replace view Quarterly_Orders 
AS
SELECT DISTINCT Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country
FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'


/****** Object:  View Invoices    Script Date: 08/12/2011 11:46:04 ******/
create or replace view Invoices 
AS
SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, 
	Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City, 
	Customers.Region, Customers.PostalCode, Customers.Country, 
	(FirstName || ' ' || LastName) AS Salesperson, 
	Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName, 
	OD.ProductID, Products.ProductName, OD.UnitPrice, OD.Quantity, 
	OD.Discount, 
	(((OD.UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight
FROM Shippers INNER JOIN 
		(Products INNER JOIN 
			(
				(Employees INNER JOIN 
					(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) 
				ON Employees.EmployeeID = Orders.EmployeeID) 
			INNER JOIN Order_Details OD ON Orders.OrderID = OD.OrderID) 
		ON Products.ProductID = OD.ProductID) 
	ON Shippers.ShipperID = Orders.shipperid

	
/****** Object:  View Product Sales for 1997    Script Date: 08/12/2011 11:46:04 ******/
create or replace view Product_Sales_for_1997 
AS
SELECT Categories.CategoryName, Products.ProductName, 
Sum(((OD.UnitPrice*Quantity*(1-Discount)/100))*100) AS ProductSales
FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) 
	INNER JOIN (Orders 
		INNER JOIN Order_Details OD ON Orders.OrderID = OD.OrderID) 
	ON Products.ProductID = OD.ProductID
WHERE (((Orders.ShippedDate) Between '19970101' And '19971231'))
GROUP BY Categories.CategoryName, Products.ProductName

/****** Object:  View Current Product List    Script Date: 08/12/2011 11:46:04 ******/
create or replace view Current_Product_List 
AS
SELECT Product_List.ProductID, Product_List.ProductName
FROM Products AS Product_List
WHERE (((Product_List.Discontinued)=0))


/****** Object:  View Order_Details Extended    Script Date: 08/12/2011 11:46:04 ******/
create or replace view Order_Details_Extended 
AS
SELECT OD.OrderID, OD.ProductID, Products.ProductName, 
	OD.UnitPrice, OD.Quantity, OD.Discount, 
	(((OD.UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice
FROM Products INNER JOIN Order_Details OD ON Products.ProductID = OD.ProductID


/****** Object:  View Products Above Average Price    Script Date: 08/12/2011 11:46:04 ******/
create or replace view Products_Above_Average_Price AS
SELECT Products.ProductName, Products.UnitPrice
FROM Products
WHERE Products.UnitPrice>(SELECT AVG(UnitPrice) From Products)
--ORDER BY Products.UnitPrice DESC


/****** Object:  View Products by Category    Script Date: 08/12/2011 11:46:04 ******/
create or replace view Products_by_Category AS
SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE Products.Discontinued <> 1
--ORDER BY Categories.CategoryName, Products.ProductName


/****** Object:  View Alphabetical list of products    Script Date: 08/12/2011 11:46:04 ******/
create or replace view Alphabetical_list_of_products AS
SELECT Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=0))


/****** Object:  View Order Subtotals    Script Date: 08/12/2011 11:46:04 ******/
create or replace view Order_Subtotals AS
SELECT OD.OrderID, Sum(((OD.UnitPrice*Quantity*(1-Discount)/100))*100) AS Subtotal
FROM Order_Details OD
GROUP BY OD.OrderID


/****** Object:  View Customer and Suppliers by City    Script Date: 08/12/2011 11:46:04 ******/
create or replace view Customer_and_Suppliers_by_City 
AS
SELECT City, CompanyName, ContactName, 'Customers' AS Relationship 
FROM Customers
UNION SELECT City, CompanyName, ContactName, 'Suppliers'
FROM Suppliers
--ORDER BY City, CompanyName


/****** Object:  View Sales Totals by Amount    Script Date: 08/12/2011 11:46:04 ******/
create or replace view Sales_Totals_by_Amount 
AS
SELECT 
  Order_Subtotals.Subtotal AS SaleAmount, Orders.OrderID, Customers.CompanyName, Orders.ShippedDate
FROM Customers INNER JOIN 
	(Orders INNER JOIN Order_Subtotals ON Orders.OrderID = Order_Subtotals.OrderID) 
	ON Customers.CustomerID = Orders.CustomerID
WHERE (Order_Subtotals.Subtotal >2500) AND (Orders.ShippedDate BETWEEN '19970101' And '19971231')


/****** Object:  View Summary of Sales by Quarter    Script Date: 08/12/2011 11:46:04 ******/
create or replace view Summary_of_Sales_by_Quarter 
AS
SELECT Orders.ShippedDate, Orders.OrderID, Order_Subtotals.Subtotal
FROM Orders INNER JOIN Order_Subtotals ON Orders.OrderID = Order_Subtotals.OrderID
WHERE Orders.ShippedDate IS NOT NULL
--ORDER BY Orders.ShippedDate


/****** Object:  View Summary of Sales by Year    Script Date: 08/12/2011 11:46:04 ******/
create or replace view Summary_of_Sales_by_Year 
AS
SELECT Orders.ShippedDate, Orders.OrderID, Order_Subtotals.Subtotal
FROM Orders INNER JOIN Order_Subtotals ON Orders.OrderID = Order_Subtotals.OrderID
WHERE Orders.ShippedDate IS NOT NULL
--ORDER BY Orders.ShippedDate


/****** Object:  View Sales by Category    Script Date: 08/12/2011 11:46:04 ******/
create or replace view Sales_by_Category 
AS
SELECT Categories.CategoryID, Categories.CategoryName, Products.ProductName, 
	Sum(ODE.ExtendedPrice) AS ProductSales
FROM 	Categories INNER JOIN 
		(Products INNER JOIN 
			(Orders INNER JOIN Order_Details_Extended ODE ON Orders.OrderID = ODE.OrderID) 
		ON Products.ProductID = ODE.ProductID) 
	ON Categories.CategoryID = Products.CategoryID
WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'
GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName
--ORDER BY Products.ProductName


/****** Object:  View Category Sales for 1997    Script Date: 08/12/2011 11:46:04 ******/
create or replace view Category_Sales_for_1997 
AS
SELECT v1997.CategoryName, Sum(v1997.ProductSales) AS CategorySales
FROM Product_Sales_for_1997 v1997
GROUP BY v1997.CategoryName






/*
create [or replace] procedure procedure_name(parameter_list)
language plpgsql
as $$
declare
-- variable declaration
begin
-- stored procedure body
end; $$
*/


/****** OK Object:  StoredProcedure SalesByCategory    Script Date: 08/12/2011 11:46:02 ******/
CREATE or replace function SalesByCategory
(
  _CategoryName varchar(15), 
  _OrdYear int
)
RETURNS TABLE 
( 
  _ProductName CHARACTER VARYING,
  _TotalPurchase decimal
)
language plpgsql
as $$
/*
  select * from SalesByCategory('Beverages',2006);
*/
declare
-- variable declaration
begin
  if (_OrdYear != 2006 AND _OrdYear != 2007 AND _OrdYear != 2008) then
	SET _OrdYear = 2006;
  end if;

  RETURN QUERY
  SELECT ProductName,
	ROUND(SUM(OD.Quantity * (1-OD.Discount) * OD.UnitPrice), 0) AS TotalPurchase
  FROM Order_Details OD, Orders O, Products P, Categories C
  WHERE OD.OrderID = O.OrderID 
	AND OD.ProductID = P.ProductID 
	AND P.CategoryID = C.CategoryID
	AND C.CategoryName = _CategoryName
	AND date_part('year', O.OrderDate) = _OrdYear
  GROUP BY ProductName
  ORDER BY ProductName;
end; $$

/****** OK Object:  StoredProcedure CustOrdersOrders    Script Date: 08/12/2011 11:46:02 ******/
create or replace function custordersorders 
(
  _customerid int
)
returns table 
( 
  _orderid int,
  _orderdate TIMESTAMP,
  _requireddate TIMESTAMP,
  _shippeddate TIMESTAMP
)
language plpgsql
as $$
/*
  select * from CustOrdersOrders(1);
*/
declare
-- variable declaration
begin
  return query
  select orderid, 
	orderdate,
	requireddate,
	shippeddate
  from orders
  where customerid = _customerid
  order by orderid;
end; $$

/****** OK Object:  StoredProcedure CustOrderHist    Script Date: 08/12/2011 11:46:01 ******/
CREATE or replace function CustOrderHist 
(
  _CustomerID int
)
RETURNS SETOF RECORD
language plpgsql
as $$
/*
  select * from CustOrderHist(1) f(ProductName CHARACTER VARYING,Total bigint)
*/
declare
-- variable declaration
begin
  RETURN QUERY
  SELECT ProductName, SUM(Quantity) Total
  FROM Products P, Order_Details OD, Orders O, Customers C
  WHERE C.CustomerID = _CustomerID
  AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
  GROUP BY ProductName;
end; $$

/****** OK Object:  StoredProcedure CustOrdersDetail    Script Date: 08/12/2011 11:46:01 ******/
CREATE or replace function CustOrdersDetail 
(
  _OrderID int
)
returns table 
( 
  _ProductName CHARACTER VARYING,
  _UnitPrice decimal,
  _Quantity smallint,
  _Discount decimal,
  _ExtendedPrice decimal
)
language plpgsql
as $$
/*
  select * from CustOrdersDetail(10666);
*/
declare
-- variable declaration
begin
  RETURN QUERY
  SELECT ProductName,
    ROUND(Od.UnitPrice, 2) UnitPrice,
    Quantity,
    (Discount * 100) Discount, 
    ROUND((Quantity * (1 - Discount) * Od.UnitPrice), 2) ExtendedPrice
  FROM Products P, Order_Details Od
  WHERE Od.ProductID = P.ProductID and Od.OrderID = _OrderID;
end; $$

/****** OK Object:  StoredProcedure Ten Most Expensive Products    Script Date: 08/12/2011 11:46:02 ******/
create or replace function Ten_Most_Expensive_Products()
returns table 
( 
  _TenMostExpensiveProducts CHARACTER VARYING,
  _UnitPrice decimal
)
language plpgsql
as $$
/*
  select * from Ten_Most_Expensive_Products();
*/
declare
-- variable declaration
begin
  RETURN QUERY
  SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
  FROM Products
  ORDER BY Products.UnitPrice DESC
  limit 10;
end; $$

/****** OK Object:  StoredProcedure Sales by Year    Script Date: 08/12/2011 11:46:02 ******/
create or replace function Sales_by_Year
( 
  _Beginning_Date TIMESTAMP, 
  _Ending_Date TIMESTAMP
)
returns table 
( 
  _ShippedDate TIMESTAMP,
  _OrderID int,
  _Subtotal decimal,
  _Year int
)
language plpgsql
as $$
/*
  select * from Sales_by_Year('2006-01-01','2006-12-31');
*/
declare
-- variable declaration
begin
  RETURN QUERY
  SELECT Orders.ShippedDate, Orders.OrderID, Order_Subtotals.Subtotal, cast(date_part('year', ShippedDate) as int) AS Year
  FROM Orders INNER JOIN Order_Subtotals ON Orders.OrderID = Order_Subtotals.OrderID
  WHERE Orders.ShippedDate Between _Beginning_Date And _Ending_Date;
end; $$

/****** OK Object:  StoredProcedure Employee Sales by Country    Script Date: 08/12/2011 11:46:02 ******/
create or replace function Employee_Sales_by_Country 
(
  _Beginning_Date TIMESTAMP, 
  _Ending_Date TIMESTAMP 
)
returns table 
( 
  _Country CHARACTER VARYING,
  _LastName CHARACTER VARYING,
  _FirstName CHARACTER VARYING,
  _ShippedDate TIMESTAMP,
  _OrderID int,
  _SaleAmount decimal
)
language plpgsql
as $$
/*
  select * from Employee_Sales_by_Country('2006-01-01','2006-12-31');
*/
declare
-- variable declaration
begin
  RETURN QUERY
  SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, Order_Subtotals.Subtotal AS SaleAmount
  FROM Employees INNER JOIN 
	(Orders INNER JOIN Order_Subtotals ON Orders.OrderID = Order_Subtotals.OrderID) 
	ON Employees.EmployeeID = Orders.EmployeeID
  WHERE Orders.ShippedDate Between _Beginning_Date And _Ending_Date;
end; $$