-- =============================================
-- Author:		<deathliger666>
-- Create date: <07.06.2016>
-- Description:	<Cursor for counting profit>
-- =============================================
CREATE PROCEDURE my_cursor(IN date1 DATE, IN date2 DATE)
  BEGIN
    DECLARE salesStart INT;
    DECLARE	buy INT;
    DECLARE	buyLeft INT;
    DECLARE	finish INT DEFAULT FALSE;
    DECLARE	PRODUCT_ID_NEW INT;
    DECLARE	ORDER_DATE_NEW DATE;
    DECLARE	QUANTITY_NEW INT;
    DECLARE	UNITCOST_NEW NUMERIC(9, 2);

    DECLARE my1 CURSOR FOR SELECT
                              PRODUCT_ID,
                              ORDER_DATE,
                              QUANTITY,
                              UNITCOST
                            FROM BUYINGS_DETAILS
                            WHERE ORDER_DATE <= date2;
    DECLARE my2 CURSOR FOR SELECT
                              PRODUCT_ID,
                              ORDER_DATE,
                              QUANTITY,
                              UNITPRICE
                            FROM SALES_DETAILS
                            WHERE ORDER_DATE <= date2;
    DECLARE HANDLER FOR NOT FOUND SET finish = TRUE;
    CREATE TABLE ResultSale(
      product_id      INT,
      items1  INT           DEFAULT 0,
      sales1  INT           DEFAULT 0,
      buyingCount    INT           DEFAULT 0,
      salesCount     INT           DEFAULT 0,
      bought          INT           DEFAULT 0,
      buyingsLEFT INT           DEFAULT 0,
      items2    INT           DEFAULT 0,
      income          NUMERIC(9, 2) DEFAULT 0,
      primary key (product_id)
    );
    INSERT INTO ResultSale(product_id) SELECT DISTINCT product_id
                                          FROM SALES_DETAILS
                                          WHERE ORDER_DATE BETWEEN date1 AND date2
                                          UNION SELECT DISTINCT product_id
                                                FROM SALES_DETAILS
                                                WHERE ORDER_DATE BETWEEN date1 AND date2;
    OPEN my2;
    OPEN my1;
   parse_loop1: LOOP
      FETCH my2
      INTO PRODUCT_ID_NEW, ORDER_DATE_NEW, QUANTITY_NEW, UNITCOST_NEW;
      IF finish
      THEN
        LEAVE parse_loop1;
      END IF;
      UPDATE ResultSale
      SET items2 = items2 - QUANTITY_NEW
      WHERE product_id = PRODUCT_ID_NEW;
      IF (ORDER_DATE_NEW < date1)
      THEN
        UPDATE ResultSale
        SET items1 = ResultSale.items1 - QUANTITY_NEW
        WHERE product_id = PRODUCT_ID_NEW;
        UPDATE ResultSale
        SET sales1 = ResultSale.sales1 + QUANTITY_NEW
        WHERE product_id = PRODUCT_ID_NEW;
      ELSE
        UPDATE ResultSale
        SET buyingsLEFT = buyingsLEFT + QUANTITY_NEW
        WHERE product_id = PRODUCT_ID_NEW;
        UPDATE ResultSale
        SET salesCount = salesCount + QUANTITY_NEW
        WHERE product_id = PRODUCT_ID_NEW;
        UPDATE ResultSale
        SET income = income + UNITCOST_NEW * QUANTITY_NEW
        WHERE product_id = PRODUCT_ID_NEW;
      END IF;
    END LOOP;
    CLOSE my2;
    SET finish = FALSE;
   parse_loop2: LOOP
      FETCH my1
      INTO PRODUCT_ID_NEW, ORDER_DATE_NEW, QUANTITY_NEW, UNITCOST_NEW;
      IF finish
      THEN
        LEAVE parse_loop2;
      END IF;
      UPDATE ResultSale
      SET items2 = items2 + QUANTITY_NEW
      WHERE product_id = PRODUCT_ID_NEW;
      IF (ORDER_DATE_NEW < date1)
      THEN
        UPDATE ResultSale
        SET items1 = ResultSale.items1 + QUANTITY_NEW
        WHERE product_id = PRODUCT_ID_NEW;
      ELSE
        UPDATE ResultSale
        SET buyingCount = buyingCount + QUANTITY_NEW
        WHERE product_id = PRODUCT_ID_NEW;
      END IF;
      SET buyLeft = (SELECT buyingsLEFT
                         FROM ResultSale
                         WHERE product_id = PRODUCT_ID_NEW);
      SET buy = (SELECT bought
                 FROM ResultSale
                 WHERE product_id = PRODUCT_ID_NEW);
      SET salesStart = (SELECT sales1
                         FROM ResultSale
                         WHERE product_id = PRODUCT_ID_NEW);
      IF (buy + QUANTITY_NEW >= salesStart)
      THEN
        UPDATE ResultSale
        SET bought = salesStart
        WHERE product_id = PRODUCT_ID_NEW;
        SET QUANTITY_NEW = QUANTITY_NEW + buy - salesStart;
        IF (QUANTITY_NEW > buyLeft)
        THEN
          UPDATE ResultSale
          SET buyingsLEFT = 0, income = income - buyLeft * UNITCOST_NEW
          WHERE product_id = PRODUCT_ID_NEW;
        ELSE
          UPDATE ResultSale
          SET buyingsLEFT = buyingsLEFT - QUANTITY_NEW, income = income - QUANTITY_NEW * UNITCOST_NEW
          WHERE product_id = PRODUCT_ID_NEW;
        END IF;
      ELSE
        UPDATE ResultSale
        SET bought = ResultSale.bought + QUANTITY_NEW
        WHERE product_id = PRODUCT_ID_NEW;
      END IF;
    END LOOP;
    CLOSE my1;
    SELECT
      product_id,
      items1,
      buyingCount,
      salesCount,
      items2,
      income
    FROM ResultSale;
  END;
CALL my_cursor('2011-06-09', '2014-10-24');

$$LANGUAGE MySQL