Welcome on my blog

USE [K_DB_Project]
GO

DELETE FROM payment;
DBCC CHECKIDENT('payment', RESEED, 0);

DELETE FROM category;
DBCC CHECKIDENT('category', RESEED, 0)

DELETE FROM product;
DBCC CHECKIDENT('product', RESEED, 0)

DELETE FROM employee;
DBCC CHECKIDENT('employee', RESEED, 0)

DELETE FROM receipt;
DBCC CHECKIDENT('receipt', RESEED, 0)

----------- product

DECLARE @count INT;
SET @count = 1;
    
WHILE @count<= 200
BEGIN
   INSERT INTO product VALUES('Product'+CAST(@count as varchar), 'Desc-'+CAST(@count as varchar), @count)
   SET @count = @count + 1;
END;



----------- department name
insert into [dbo].[department] values
('HR'),
('Sprzedaży'),
('Dostaw'),
('Testowania'),
('Administracji'),
('Programowania')

----------- category
insert into [dbo].[category] values
('Budowlany','2'),
('Sanitarny','2'),
('Metalowy','1'),
('Narzędziowy','4'),
('Farby','1'),
('Hydrauliczny','3'),
('Elektryczny','2'),
('Oświetlenie','2'),
('Dywany','3'),
('Kuchnia','2'),
('Łazienka','1'),
('Płytki','2'),
('Ogród','3'),
('Okna','1'),
('Drzwi','5'),
('Panele','3'),
('Dekarski','1'),
('Izolacja','3'),
('Ogrzewanie','2'),
('Wentylacja','1'),
('Bramy','2')



insert into [dbo].[employee] values
('Roberto','Lewandowski','2'),
('Gail','Matias','2'),
('Dylan','Fenix','1'),
('Latisha','Mathis','2'),
('Eric','Short','2'),
('Tracy','Cash','3'),
('Patsy','Mccarty','2'),
('Ilayda','Schaefer','2'),
('Muskaan','Bridges','3'),
('Willis','Norris','2'),
('Ruairi','Wall','3'),
('Samiya','Terrell','2'),
('Millie','Howard','4'),
('Dione','Tyler','1'),
('Karl','Zimmerman','2'),
('Momina','Humphreys','5'),
('Barbara','Plant','2'),
('Jayden','Richard','1'),
('Janet','Marks','1'),
('Shahid','Flower','1'),
('Dylan','Fenix','2')



----------- date_info 
DELETE FROM payment;

DECLARE @StartDate  date = '20210101';

DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 2, @StartDate));

;WITH seq(n) AS 
(
  SELECT 0 UNION ALL SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d) AS 
(
  SELECT DATEADD(DAY, n, @StartDate) FROM seq
),
src AS
(
  SELECT
    date_full         = CONVERT(date, d),
    date_day          = DATEPART(DAY,       d),
    date_month        = DATEPART(MONTH,     d),
    date_year         = DATEPART(YEAR,      d)
  FROM d
),
dim AS
(
  SELECT
    date_full, 
    date_day,
    date_month,
    date_year,
    Style101            = CONVERT(char(10), date_full, 101),
    Style103            = CONVERT(char(10), date_full, 103),
    Style112            = CONVERT(char(8),  date_full, 112),
    Style120            = CONVERT(char(10), date_full, 120)
  FROM src
)
  
  
--   SELECT date_full,date_day,date_month,date_year FROM dim
--   ORDER BY date_day
--   OPTION (MAXRECURSION 0);

  INSERT INTO payment SELECT (date_day*date_month+date_year),date_full FROM dim ORDER BY date_day OPTION (MAXRECURSION 0);


----------- receipt

-- DECLARE @licz INT;
-- SET @licz = 0;
-- WHILE @licz <= 20
-- begin
--     SELECT FLOOR(RAND()*(100-0)+0);
--     SET @licz = @licz + 1;
-- END

DECLARE @licz INT;

DECLARE @product_ID INT;
DECLARE @product_COUNT INT;
SET @product_COUNT = (SELECT COUNT(1) FROM product);

DECLARE @payme_ID INT;
DECLARE @payme_COUNT INT;
SET @payme_COUNT = (SELECT COUNT(1) FROM payment);


DECLARE @employee_ID INT;
DECLARE @employee_COUNT INT;
SET @employee_COUNT = (SELECT COUNT(1) FROM employee);

DECLARE @price INT;
DECLARE @discount INT;
DECLARE @amount INT;

SET @licz = 0;
WHILE @licz <= 20
begin
    SET @product_ID = FLOOR(RAND()*(@product_COUNT-1)+1);
    SET @payme_ID = FLOOR(RAND()*(@payme_COUNT-1)+1);
    SET @employee_ID = FLOOR(RAND()*(@employee_COUNT-1)+1);
    SET @amount = FLOOR(RAND()*(150-2)+2);
    SET @discount = FLOOR(RAND()*(80-20)+1);
    SEt @price = @amount-(@discount%@amount);
    ---INSERT INTO [dbo].[receipt] VALUES(1, 1, 1, 1, 1, 1,1,1);
    INSERT INTO [dbo].[receipt] VALUES('12-12-2012', @product_ID,  @employee_ID, @amount,@price,@discount,@payme_ID);
    SET @licz = @licz + 1;
END

-- Utwórz procedurę składowaną do wypełnienia tabeli storage
DELIMITER //
CREATE PROCEDURE FillStorageTable()
BEGIN
    DECLARE i INT DEFAULT 1;
    
    WHILE i <= 50 DO
        INSERT INTO `storage` (`employee_id`, `client_id`, `metal_id`, `weight`)
        VALUES (i, i, FLOOR(RAND() * 8) + 1, FLOOR(RAND() * 1000) + 1);
        
        SET i = i + 1;
    END WHILE;
    
END //
DELIMITER ;

-- Wywołaj procedurę składowaną
CALL FillStorageTable();