/*
* Inteligencia de negocio - / script dtl
* @autor: hburgos089@gmail.com
* @version: 1.0.0
* fecha: 07-10-2019
*/

USE `datawarehouse`;

/*--- limpiar las tablas de datos antes de la recarga de nueva informacion ---*/
DELETE
FROM d_tiempo;
DELETE
FROM d_productos;
DELETE
FROM d_clientes;
DELETE
FROM d_oficinas;
DELETE
FROM tabla_hechos;
/*--- creando tabla de tiempos ---*/
INSERT into d_tiempo(d_tiempo.idTiempo, d_tiempo.fecha, d_tiempo.anio, d_tiempo.mes, d_tiempo.semana, d_tiempo.dia)
SELECT YEAR(classicmodels.orders.orderDate) * 10000 + MONTH(classicmodels.orders.orderDate) * 100 +
       DAY(classicmodels.orders.orderDate),
       classicmodels.orders.orderDate,
       YEAR(classicmodels.orders.orderDate),
       MONTH(classicmodels.orders.orderDate),
       WEEK(classicmodels.orders.orderDate),
       DAY(classicmodels.orders.orderDate)
FROM classicmodels.orders
GROUP BY classicmodels.orders.orderDate;
/*--- creando tabla de productos ---*/
INSERT INTO d_productos(d_productos.idProducto, d_productos.nombre, d_productos.categoria, d_productos.precioCompra,
                        d_productos.precioVenta)
SELECT classicmodels.products.productCode,
       classicmodels.products.productName,
       classicmodels.products.productLine,
       classicmodels.products.buyPrice,
       classicmodels.products.MSRP
FROM classicmodels.products;
/*--- creando tabla de clientes ---*/
INSERT INTO d_clientes(d_clientes.idCliente, d_clientes.nombre, d_clientes.ciudad, d_clientes.pais)
SELECT classicmodels.customers.customerNumber,
       classicmodels.customers.customerName,
       classicmodels.customers.city,
       classicmodels.customers.country
FROM classicmodels.customers;
/*--- creando tabla de oficinas ---*/
INSERT INTO d_oficinas(d_oficinas.idOficina, d_oficinas.direccion, d_oficinas.ciudad, d_oficinas.pais)
SELECT classicmodels.offices.officeCode,
       classicmodels.offices.addressLine1,
       classicmodels.offices.city,
       classicmodels.offices.country
FROM classicmodels.offices;
/*--- creando tabla de hechos ---*/
INSERT INTO tabla_hechos(idOrden, idTiempo, idProducto, idOficina, idCliente, cantidad, precioNeto, precioVenta,
                         precioSugerido)
SELECT classicmodels.orders.orderNumber           AS idOrden,
       YEAR(classicmodels.orders.orderDate) * 10000 + MONTH(classicmodels.orders.orderDate) * 100 +
       DAY(classicmodels.orders.orderDate)        as idTiempo,
       classicmodels.products.productCode         as idProducto,
       classicmodels.offices.officeCode           as idOficina,
       classicmodels.customers.customerNumber     as idCliente,
       classicmodels.orderdetails.quantityOrdered as cantidad,
       classicmodels.products.buyPrice*classicmodels.orderdetails.quantityOrdered as precioNeto,
       classicmodels.orderdetails.priceEach*classicmodels.orderdetails.quantityOrdered       as precioVenta,
       classicmodels.products.MSRP*classicmodels.orderdetails.quantityOrdered
FROM classicmodels.orders,
     classicmodels.orderdetails,
     classicmodels.offices,
     classicmodels.products,
     classicmodels.customers,
     classicmodels.employees
WHERE classicmodels.orderdetails.orderNumber = classicmodels.orders.orderNumber
  AND classicmodels.orderdetails.productCode = classicmodels.products.productCode
  AND classicmodels.orders.customerNumber = classicmodels.customers.customerNumber
  AND classicmodels.customers.salesRepEmployeeNumber = classicmodels.employees.employeeNumber
  AND classicmodels.employees.officeCode = classicmodels.offices.officeCode;

/*--- vista de tabla de hechos ---*/
SELECT tabla_hechos.idOrden,tabla_hechos.idTiempo,d_tiempo.fecha,d_tiempo.anio,d_tiempo.mes,d_tiempo.semana,d_tiempo.dia,
       tabla_hechos.idProducto, d_productos.nombre as nombreProducto, d_productos.categoria as categoriaProducto,tabla_hechos.idOficina,
       d_oficinas.direccion as direccionOficina,d_oficinas.ciudad as ciudadOficina, d_oficinas.pais as paisOficina,tabla_hechos.idCliente,
       d_clientes.nombre as nombreCliente, d_clientes.ciudad as ciudadCliente, d_clientes.pais as paisCliente,
       tabla_hechos.cantidad,tabla_hechos.precioNeto, tabla_hechos.precioVenta, tabla_hechos.precioSugerido
FROM d_clientes,d_oficinas,d_productos,d_tiempo,tabla_hechos
WHERE tabla_hechos.idTiempo=d_tiempo.idTiempo AND tabla_hechos.idProducto=d_productos.idProducto AND
        tabla_hechos.idOficina=d_oficinas.idOficina AND tabla_hechos.idCliente=d_clientes.idCliente
