CwKbwYKP

1DROP TABLE IF EXISTS Debug;
2DROP TABLE IF EXISTS TempTable;
3
4DROP PROCEDURE IF EXISTS CheckoutItems;
5DROP PROCEDURE IF EXISTS CheckoutItem;
6delimiter $$
7
8CREATE PROCEDURE CheckoutItem (item_id int(6), check_id int(6), out success bool, out item_price int)
9begin
10    select amount_available into @items_available
11    from ClothingInstances where id = item_id;
12    
13    if (select amount_available from ClothingInstances where id = item_id) > 0 then
14        # decrement amount_available
15        update ClothingInstances set amount_available = amount_available - 1 where id = item_id;
16    
17        set item_price = (select selling_price from ClothingInstances where id = item_id);
18        set @wholesale_price = (select wholesale_price from ClothingInstances where id = item_id);
19
20        # create entry in SoldItems
21        insert into SoldItems(check_id, instances_id, selling_price, earnings)
22        values(check_id, item_id, selling_price, selling_price - @wholesale_price);
23        set success = True;
24    else
25        set success = False;
26    end if;
27end$$
28
29
30CREATE TABLE IF NOT EXISTS Debug (log varchar(30));
31CREATE TABLE IF NOT EXISTS TempTable (ID INT);
32
33CREATE PROCEDURE CheckoutItems (item_ids varchar(30), seller_id int(6), cashbox_id int(6))
34begin
35    # variables for iteration through TempTable
36    DECLARE done BOOLEAN DEFAULT FALSE;
37    DECLARE _ID BIGINT UNSIGNED;
38    DECLARE cur CURSOR FOR SELECT ID FROM TestTable;
39    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;
40
41    # fill TempTable with items to checkout id's
42    WHILE LENGTH(item_ids) > 0 DO
43        IF NOT INSTR(item_ids, ',') > 0 THEN # <item_ids contains comma>
44            INSERT INTO Debug(log) VALUES ("p1");
45            INSERT INTO TempTable(ID) VALUES (CAST(item_ids as UNSIGNED));
46            SET item_ids = '';
47        ELSE
48            INSERT INTO Debug(log) VALUES ("p2-b");
49            INSERT INTO Debug(log) VALUES (SUBSTRING_INDEX(item_ids, ",", 1));
50            INSERT INTO TempTable(ID) VALUES (CAST(SUBSTRING_INDEX(item_ids, ",", 1) AS UNSIGNED)); # <part until next comma>)
51            SET item_ids = SUBSTRING(item_ids, INSTR(item_ids, ',') + 1, LENGTH(item_ids)); # <part after next comma>
52            INSERT INTO Debug(log) VALUES (item_ids);
53            INSERT INTO Debug(log) VALUES ("p2-e");
54        END IF;
55    END WHILE;
56    # at this point we have finished filling TempTable with items to checkout id's
57
58    INSERT INTO Checks(seller_id, total_price, amount_of_items, cashbox_id, checkout_date)
59    VALUES (seller_id, 0, 0, cashbox_id, CURDATE());
60    set @check_id = (select LAST_INSERT_ID());
61
62    # iterate through items in TempTable
63
64    set @amount = 0;
65    set @total_price = 0;
66    OPEN cur;
67    testLoop: LOOP
68        FETCH cur INTO _id;
69        IF done THEN
70            LEAVE testLoop;
71        END IF;
72        CALL CheckoutItem(_id, @check_id, @success, @item_price);
73        IF @succcess THEN
74            set @amount = @amount + 1;
75            set @total_price = @total_price + @item_price;
76        END IF;
77    END LOOP testLoop;
78    CLOSE cur;
79    update Checks
80    set total_price = @total_price,
81        amount_of_items = @amount
82    where id = @check_id;
83end$$
84
85delimiter ;