tjGM2KbV

1create procedure sessionReport(dateFrom datetime, dateTo datetime)
2begin
3  declare sessionF1 varchar(100);
4  declare sessionF2 int;
5  declare sessionF3 int;
6  declare sessionF4 varchar(100);
7  declare sessionStart datetime;
8  declare sessionEnd datetime;
9
10  declare rowF1 varchar(100);
11  declare rowF2 int;
12  declare rowF3 int;
13  declare rowF4 varchar(100);
14  declare rowTime datetime;
15
16  declare done int default false;
17
18  declare curInput cursor for
19    select device_id, scan_count, controller_id, train_no, `time` 
20    from control_device_info
21    where `time` >= dateFrom and `time` < dateTo
22    order by controller_id, `time`
23    ;
24
25  declare continue handler for not found set done = true;
26
27  drop temporary table if exists temp_table;
28  create temporary table temp_table (
29    f1 varchar(100),
30    f2 int,
31    f3 int,
32    f4 varchar(100),
33    start_time datetime,
34    end_time datetime
35  ) engine=Memory;
36
37  open curInput;
38
39  read_loop: loop
40    fetch curInput into rowF1, rowF2, rowF3, rowF4, rowTime;
41    if done then 
42      leave read_loop;
43    end if;
44
45   if sessionF1 is null or rowF1 <> sessionF1 or rowF3 <> sessionF3 or rowF4 <> sessionF4 then
46      if sessionF1 is not null then
47        insert into temp_table (f1, f2, f3, f4, start_time, end_time)
48          values (sessionF1, sessionF2, sessionF3, sessionF4, sessionStart, sessionEnd);
49      end if;
50
51      set
52        sessionF1 = rowF1,
53        sessionF3 = rowF3,
54        sessionF4 = rowF4,
55        sessionF2 = rowF2,
56        sessionStart = rowTime,
57        sessionEnd = rowTime;      
58    else
59      set
60        sessionF2 = case when rowF2 < sessionF2  then sessionF2 else rowF2 end,
61        sessionEnd = rowTime;    
62    end if;
63
64
65  end loop;
66
67  close curInput;
68
69   if sessionF1 is not null then
70    insert into temp_table (device_id, scan_count, controller_id, train_no, start_time, end_time)
71      values (sessionF1, sessionF2, sessionF3, sessionF4, sessionStart, sessionEnd);
72  end if;
73
74  select * from temp_table;
75end;