-------------------------------------------------------------------------------
-- Data Mart - Encomendas
-- Data Mart - Qualidade de Servi?o
-------------------------------------------------------------------------------
-- Inicializa??o do DW
-- Limpeza das tabelas de factos
-------------------------------------------------------------------------------

DELETE NorthwindDW.TF_Encomendas;
DELETE NorthwindDW.TF_QualidadeServico;

-------------------------------------------------------------------------------
-- Limpeza das tabelas de dimens?es no DW
--
DELETE NorthwindDW.Dim_Fonte;
DELETE NorthwindDW.Dim_Calendario;
DELETE NorthwindDW.Dim_Cliente;
DELETE NorthwindDW.Dim_Produto;
DELETE NorthwindDW.Dim_Categoria;
DELETE NorthwindDW.Dim_Funcionario;

-------------------------------------------------------------------------------
-- Povoamento das tabelas de dimens?es
-------------------------------------------------------------------------------
-- Povoamento inicial da dimens?es ?Dim_Calendario 

declare
DATAINI date;
DATAFIM date;
begin
SELECT min(to_date(to_char(nvl(Orderdate,sysdate),'mm/dd/yyyy'), 'mm/dd/yyyy')) into DATAINI FROM Northwind.Orders;
SELECT max(to_date(to_char(nvl(Orderdate,sysdate),'mm/dd/yyyy'), 'mm/dd/yyyy')) into DATAFIM FROM Northwind.Orders;
WHILE DATAINI <= DATAFIM loop
	INSERT INTO NorthwindDW.Dim_Calendario
	(Data, DiaSemana, Semana, Mes, Trimestre, Semestre, Ano)
	VALUES(DATAINI, 
		to_char(DATAINI,'DAY'),
		to_number(to_char(DATAINI,'WW')),
		to_char(DATAINI,'MONTH'),
		to_number(to_char(DATAINI,'q')),
		CASE to_number(to_char(DATAINI,'q'))
			WHEN 1 then 1 
			WHEN 2 then 1 
			ELSE 2 
		END,
		to_number(to_char(DATAINI,'yyyy')));
		DATAINI :=  DATAINI +1;
END loop;
end;

-------------------------------------------------------------------------------
-- Povoamento inicial da dimens?o "Dim_Fonte"
--
INSERT INTO NorthwindDW.Dim_Fonte 
	(ID, DESIGNACAO, DESCRICAO, OBSERVACOES)
	VALUES(1,'Northwind - Sistema Operacional','Sistema principal da empresa.','Nada a assinalar.');
INSERT INTO NorthwindDW.Dim_Fonte
	(ID, DESIGNACAO, DESCRICAO, OBSERVACOES)
	VALUES(2,'Northwind - Agente de Vendas','Sistema ambulante de tratamento de encomendas.','Nada a assinalar.');


-- Povoamento inicial da dimens?o "Dim_Cliente" 
--
INSERT INTO NorthwindDW.Dim_Cliente (ID, DESIGNACAO, TELEFONE, CIDADE, PAIS, OBSERVACOES)
	SELECT CustomerId, CompanyName, Phone, City, Country, 'Nada a Assinalar'
		FROM Northwind.Customers;

-- Povoamento inicial da dimens?o "Dim_Categoria" 
--
INSERT INTO NorthwindDW.Dim_Categoria (ID, DESIGNACAO, OBSERVACOES)
	SELECT CategoryID, CategoryName, 'Nada a Assinalar'
		FROM Northwind.Categories;

-- Povoamento inicial da dimens?o "Dim_Produto" 
--
INSERT INTO NorthwindDW.Dim_Produto (ID, DESIGNACAO, CATEGORIA, OBSERVACOES)
	SELECT ProductId, ProductName, CategoryID, 'Nada a Assinalar'
		FROM Northwind.Products;

-- Povoamento inicial da dimens?o "Dim_Funcionario" 
--
INSERT INTO NorthwindDW.Dim_Funcionario (ID, NOME, ENDERECO, CIDADE, REGIAO, PAIS, OBSERVACOES)
	SELECT EmployeeID, FirstName||LastName, Address, City, 
		CASE WHEN Region IS NOT NULL
			THEN Region
			ELSE 'Nao definido'
		END, 
		Country, 'Nada a Assinalar'
		FROM Northwind.Employees;


-------------------------------------------------------------------------------
-- Povoamento das tabelas de factos
-------------------------------------------------------------------------------
-- Povoamento inicial da tabela "TF_Encomendas"
--
INSERT INTO NorthwindDW.TF_Encomendas (FONTE, ENCOMENDA, DATAENCOMENDA,
FUNCIONARIO, CLIENTE, PRODUTO, QUANTIDADE, PRECO, VALORTOTAL, VALORDESCONTO,
VALORTOTALCOMDESCONTO)
	SELECT 1, EN.OrderID, OrderDate, EmployeeID, CustomerID, ProductId, 
		Quantity, UnitPrice, Quantity*UnitPrice AS TotalValue,
		Quantity*UnitPrice*Discount AS DiscountValue,
		(Quantity*UnitPrice)-(Quantity*UnitPrice*Discount) AS TotalWDiscountValue
		FROM Northwind.Orders EN INNER JOIN Northwind.Order_Details LE
			ON EN.OrderId = LE.OrderId;

-------------------------------------------------------------------------------
-- Povoamento inicial da tabela "TF_QualidadeServico"

INSERT INTO NorthwindDW.TF_QualidadeServico (ENCOMENDA, DATAENCOMENDA, DATAPARAENTREGAR, DATADESPACHO, FUNCIONARIO, CLIENTE, DIASATEPEDIDOENTREGA, DIASATEDESPACHO, INDICERELACAO)
	SELECT OrderID, OrderDate, RequiredDate, ShippedDate, EmployeeID, CustomerID,
trunc(OrderDate-RequiredDate),
trunc(OrderDate-ShippedDate),
		CASE WHEN  (OrderDate-RequiredDate) - (OrderDate-ShippedDate) < 0
			THEN -1
			ELSE 1
		END
		FROM Northwind.Orders where ShippedDate is not null and RequiredDate is not null;