dbtutorials

CREATE VIEW Cityinfo AS -- krijimi i nje Pamjeje ku listohen te gjithe konsumatoret dhe furnizuesit ne qytetet perkatese

SELECT City, CompanyName, ContactName, 'Customer' AS Relationship
FROM customers

UNION

SELECT City, CompanyName, ContactName, 'Supplier' AS Relationship
FROM Suppliers ;




DELIMITER $$  -- Procedura per nxjerrjen e nje raporti per konsumatorin (numrin e porosive), (Shpenzimi total i realizuar)
-- Daten porosise se pare te realizuar, Daten e porosise se fundit te realizuar

DROP PROCEDURE IF EXISTS Cus_info $$

CREATE PROCEDURE Cus_info (IN Cus_ID CHAR(5))
BEGIN

DECLARE Nr_Orders INT;
    DECLARE Total_amount FLOAT;
    DECLARE First_date DATE;
    DECLARE Last_date DATE;
   
    SELECT COUNT(OrderID) INTO Nr_orders
    FROM orders
    WHERE CustomerID = Cus_ID;
   
    SELECT SUM(b.UnitPrice * b.Quantity * (1-b.Discount)) INTO Total_amount
    FROM orders AS a
INNER JOIN
order_details AS b ON a.OrderID = b.OrderID
WHERE a.CustomerID = Cus_ID;
   
    SELECT MIN(OrderDate) INTO First_date
    FROM orders
    WHERE CustomerID = Cus_ID;
   
    SELECT MAX(OrderDate) INTO Last_date
    FROM orders
    WHERE CustomerID = Cus_ID;
   
    SELECT CustomerID, CompanyName, Nr_Orders, Total_amount, First_date,
Last_date
FROM Customers
    WHERE CustomerID = Cus_ID;

END $$

DELIMITER ;
 -- -------------------------------------------------------------------------------------------------------------




DELIMITER $$ -- Procedure qe kerkon ne baze te emrit per nr total te porosive te realizuar nga secili punonjes

DROP PROCEDURE IF EXISTS Search_emp $$

CREATE PROCEDURE Search_emp (IN FName VARCHAR(25))
BEGIN
SELECT CONCAT(b.EmployeeID,'-', b.FirstName,' ', b.LastName) AS Employee,
Count(a.OrderID) AS Num_orders
FROM orders AS a
INNER JOIN
employees AS b ON a.EmployeeID = b.EmployeeID
WHERE b.FirstName LIKE CONCAT('%',FName,'%')
GROUP BY b.EmployeeID;

END $$

DELIMITER ;
-- -----------------------------------------------------------------------------------------------------------------



DELIMITER $$ -- procedure qe nxjerr 5 produktet me te shitura ne vitin e dhene

DROP PROCEDURE IF EXISTS Max_Product $$

CREATE PROCEDURE Max_Product (IN Prod_Year YEAR)
BEGIN
SELECT b.ProductID, a.ProductName, SUM(b.Quantity) AS Prod_order
    FROM products AS a
INNER JOIN
order_details AS b ON b.ProductID = a.ProductID
INNER JOIN
orders AS c ON c.OrderID = b.OrderID
WHERE YEAR(c.OrderDate) = Prod_Year
    GROUP BY b.ProductID
    ORDER BY Prod_order DESC
    LIMIT 5;

END $$

DELIMITER ;
-- ------------------------------------------------------------------------------------------------------------------



DELIMITER $$ -- procedure qe nxjerr numrin e porosive te realizuar nga secili punetor ne harkun kohor te dy datave

DROP PROCEDURE IF EXISTS Emp_orders $$

CREATE PROCEDURE Emp_orders (IN Start_date DATE, IN End_date DATE)
BEGIN

    SELECT a.EmployeeID, a.FirstName, a.LastName,
COUNT(b.OrderID) AS Nr_orders
    FROM employees AS a
LEFT OUTER JOIN
orders AS b ON a.EmployeeID = b.EmployeeID
WHERE b.OrderDate BETWEEN Start_date AND End_Date
    GROUP BY a.EmployeeID;

END $$

DELIMITER ;