f8t81A88

1SET @start = '2016-12-12'; -- Новая дата заезда
2SET @end = '2016-12-20'; -- Новая дата выезда
3SELECT a.nomer
4FROM allnomer a
5LEFT JOIN main m
6  ON a.nomer = m.numbernomer
7  AND DATEDIFF(m.datestart, @end) * DATEDIFF(m.dateend, @start) <= 0
8WHERE a.type = 'lux' AND m.numbernomer IS NULL
9LIMIT 1
10	
11declare @start date = '2016-12-12'; -- Новая дата заезда
12declare @end date = '2016-12-20'; -- Новая дата выезда
13declare @today date = '2016-12-11';
14
15update  main 
16set     numbernomer = null
17where   datestart > @today;
18
19declare @mainnumber int;
20
21while exists (select * from main where numbernomer is null)
22begin
23
24    select top(1) @mainnumber = id
25    from    main
26    where   numbernomer is null
27    order by datestart asc, datediff(day, datestart, dateend) desc;
28
29    update m0
30    set     numbernomer = m1.nomer
31    from    main m0
32            cross apply (SELECT top 1 a.nomer
33                        FROM    allnomer a
34                                LEFT JOIN main m
35                                ON a.nomer = m.numbernomer
36                                AND DATEDIFF(day, m.datestart, m0.dateend) * DATEDIFF(day, m.dateend, m0.datestart) <= 0
37                                left join (select m2.numbernomer, max(m2.dateend) as dateend from main m2 group by m2.numbernomer) as m2
38                                        on a.nomer = m2.numbernomer
39                        WHERE a.type = 'lux' AND m.numbernomer IS NULL
40                        order by datediff(day, m2.dateend, m0.datestart), a.nomer
41                        ) as m1
42    where   m0.id = @mainnumber;
43
44end;
45
46SELECT a.nomer
47FROM allnomer a
48LEFT JOIN main m
49  ON a.nomer = m.numbernomer
50  AND DATEDIFF(day, m.datestart, @end) * DATEDIFF(day, m.dateend, @start) <= 0
51WHERE a.type = 'lux' AND m.numbernomer IS NULL;
52	
53CREATE PROCEDURE pack()
54BEGIN
55    DECLARE done INT DEFAULT FALSE;
56    DECLARE d_start DATE;
57    DECLARE d_end DATE;
58    DECLARE num varchar(7);
59    DECLARE cur CURSOR FOR SELECT datestart, dateend FROM main ORDER BY 1, 2 DESC;
60    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
61    CREATE TEMPORARY TABLE appts(nomer varchar (7), free_from DATE) ENGINE = Memory;
62    INSERT INTO appts(nomer, free_from) SELECT nomer, 0 FROM allnomer;
63    CREATE TEMPORARY TABLE shedule(nomer varchar (7), datestart DATE, dateend DATE) ENGINE = Memory;
64    OPEN cur;
65read_loop:
66    LOOP    
67        FETCH cur INTO d_start, d_end;
68        IF done THEN
69            LEAVE read_loop;
70        END IF; 
71        SELECT a.nomer INTO num 
72            FROM appts a
73            WHERE a.free_from < d_start 
74            ORDER BY 1 
75            LIMIT 1;    
76        INSERT INTO shedule 
77            SELECT num, d_start, d_end;
78        UPDATE appts a
79            SET a.free_from = d_end
80            WHERE a.nomer = num;
81    END LOOP read_loop;
82    CLOSE cur;
83    SELECT nomer, datestart, dateend 
84        FROM shedule
85        ORDER BY 1, 2;
86    DROP TEMPORARY TABLE shedule;
87    DROP TEMPORARY TABLE appts;
88END;
89	
90set @today:=date('2016-12-11');
91update main M
92  join (
93   select cid, nomer
94     from (
95       select A.nomer,
96              @cstart:=if(@cnum=A.nomer,@cstart,A.start),
97              @cnum:=A.nomer,
98              @cid:=(select M.id
99                       from main M,allnomer MT
100                      where MT.nomer=M.numbernomer and MT.type='lux'
101                        and M.datestart>@cstart
102                        and find_in_set(M.id,@used)=0
103                      order by datestart
104                      limit 1
105                    ) cid,
106              @cstart:=(select dateend from main where id=@cid) dend,
107              @used:=coalesce(concat(@used,',',@cid),@used)
108         from (
109          select A.nomer, A.start
110            from (
111              select A.nomer,
112                     (select coalesce(min(dateend),@today-interval 1 day)
113                        from main M
114                       where datestart<=@today and M.numbernomer=A.nomer) start
115                from allnomer A where type='lux'
116            ) A,
117            main M, allnomer MT
118           where MT.nomer=M.numbernomer and MT.type='lux' and M.datestart>A.start
119           order by A.start desc, A.nomer
120         ) A,
121         (select @cid:=0,@cnum:=0,@cstart:=NULL,@used:='') Y
122     ) X where cid is not null
123  ) U
124  on M.id=U.cid
125 set M.numbernomer=U.nomer