FIFO metodu ilə satılmış məhsulların maya dəyərini hesablamaq

DELIMITER $$
CREATE DEFINER=`u623198832_pos`@`127.0.0.1` FUNCTION `calculateSalesItemCost`(stock_id INT, input_date DATE) RETURNS decimal(16,2)
    READS SQL DATA
BEGIN
    DECLARE sold BIGINT DEFAULT 0;
    DECLARE inpItemCount INT;
    DECLARE inpItemCost DECIMAL(16,2);
    DECLARE cost DECIMAL(16,2) DEFAULT 0;
    DECLARE done INT DEFAULT 0;

    DECLARE cur_purchase CURSOR FOR
        SELECT SUM(itp.count) AS Say, itp.maya_deyeri
        FROM input_products itp
        JOIN invoices inv ON inv.id = itp.invoice_id
        WHERE itp.stock_id = stock_id
        GROUP BY itp.maya_deyeri, inv.date
        ORDER BY inv.date ASC;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    IF input_date IS NULL THEN
        SET input_date = CURDATE();
    END IF;

    -- Calculate sold items count
    SET sold = (SELECT IFNULL(SUM(sl.count), 0) FROM sales sl WHERE sl.finished = 1 AND sl.stock_id = stock_id AND DATE(sl.updated_at) <= DATE(input_date));

    OPEN cur_purchase;

    read_loop: LOOP
        FETCH cur_purchase INTO inpItemCount, inpItemCost;
        IF done THEN
            LEAVE read_loop;
        END IF;

        IF sold <= inpItemCount THEN
            SET cost = cost + (sold * inpItemCost);
            SET sold = 0;
        ELSE
            SET cost = cost + (inpItemCount * inpItemCost);
            SET sold = sold - inpItemCount;
        END IF;
    END LOOP;

    CLOSE cur_purchase;

    RETURN cost;
END$$
DELIMITER ;

$startYMD = Carbon::parse($start)->format('Y-m-d');
$endYMD = Carbon::parse($end)->format('Y-m-d');

$t1_saled_cost = DB::table(DB::raw('(SELECT
sl.stock_id,
SUM(sl.count) AS say,
calculateSalesItemCost(sl.stock_id, "'.$startYMD.'") AS MayaDeyeri
FROM sales sl
GROUP BY sl.stock_id
) as sub'))
    ->select(DB::raw('SUM(MayaDeyeri) as total_cost'))
    ->first();

$t2_saled_cost = DB::table(DB::raw('(SELECT
sl.stock_id,
SUM(sl.count) AS say,
calculateSalesItemCost(sl.stock_id, "'.$endYMD.'") AS MayaDeyeri
FROM sales sl
GROUP BY sl.stock_id
) as sub'))
    ->select(DB::raw('SUM(MayaDeyeri) as total_cost'))
    ->first();