yn8nkKr0

1
2#drop table if exists refundbookings;
3#create table refundbookings (BookingId int not null);
4
5# save off the booking id's
6insert into refundbookings
7select r.BookingId
8from BookingFee bf
9  join Booking b on bf.BookingId=b.BookingId
10  join Refund r on bf.RefundId=r.Id
11where bf.BookingId <> r.BookingId and bf.Deleted is null and bf.type=0
12union
13select bf.BookingId
14from BookingFee bf
15  join Booking b on bf.BookingId=b.BookingId
16  join Refund r on bf.RefundId=r.Id
17where bf.BookingId <> r.BookingId and bf.Deleted is null and bf.type=0
18union
19select r.BookingId
20from Refund r
21where Deleted is null and voided is null and w_fee != 0 and not exists (select * from BookingFee bf where bf.RefundId=r.Id and bf.deleted is null);
22
23# create new fees
24insert into BookingFee (BookingId, RefundId, Description, Amount, OwnerCommissionPercent, OwnerAmount, CommissionAmount, Type, Created, ListingSiteToken)
25select r.BookingId, r.Id, 'GENERATED Refund Fee', r.w_Fee, 0, 0, 0, 0, 0, CONCAT(r.ListingSiteToken, ':payment')
26from BookingFee bf
27  join Booking b on bf.BookingId=b.BookingId
28  join Refund r on bf.RefundId=r.Id
29where bf.BookingId <> r.BookingId and bf.Deleted is null and bf.type=0 and r.deleted is null and r.voided is null;
30
31# expenses to create
32# STOP AND CAPTURE OR IT WON'T BE AVAILABLE LATER
33select r.BookingId, r.Id, r.UserId, r.w_Fee
34from BookingFee bf
35  join Booking b on bf.BookingId=b.BookingId
36  join Refund r on bf.RefundId=r.Id
37where bf.BookingId <> r.BookingId and bf.Deleted is null and bf.type=0 and r.deleted is null and r.voided is null
38#and exists (select * from Refund r2 where r2.BookingId=b.BookingId and r2.deleted is null and r2.w_Fee = bf.Amount and not exists (select * from BookingFee b2 where b2.RefundId=r2.Id and b2.deleted is null))
39and (exists (select * from BookingFee bf2 where bf2.bookingid=r.bookingid and bf2.type = 0 and deleted is null and not bf2.ExpenseId is null) or not exists (select * from BookingFee bf2 where bf2.bookingid=r.bookingid and bf2.type = 0 and deleted is null))
40and exists (select * from Expense where bookingid=r.bookingid and voided is null and deleted is null)
41order by r.UserId
42
43#  stuff to remap
44update BookingFee bf
45join (
46  select bf.Id as BookingFeeId, (select r2.Id from Refund r2 where r2.BookingId=bf.BookingId and r2.deleted is null and r2.w_Fee = bf.Amount and not exists (select * from BookingFee b2 where b2.RefundId=r2.Id and b2.deleted is null)) as NewRefundId
47  from BookingFee bf
48    join Booking b on bf.BookingId=b.BookingId
49    join Refund r on bf.RefundId=r.Id
50  where bf.BookingId <> r.BookingId and bf.Deleted is null and bf.type=0
51  and exists (select * from Refund r2 where r2.BookingId=b.BookingId and r2.deleted is null and r2.w_Fee = bf.Amount and not exists (select * from BookingFee b2 where b2.RefundId=r2.Id and b2.deleted is null))
52) x on x.BookingFeeId=bf.Id
53set bf.RefundId=x.NewRefundId;
54
55# expenses to create (new fees that never existed)
56# STOP AND CAPTURE OR IT WON'T BE AVAILABLE LATER
57select r.BookingId, r.Id, r.UserId, r.w_Fee
58from Refund r
59  left join BookingFee bf on bf.RefundId=r.Id
60where bf.Id is null and r.deleted is null and r.voided is null and w_Fee != 0
61#and exists (select * from Refund r2 where r2.BookingId=b.BookingId and r2.deleted is null and r2.w_Fee = bf.Amount and not exists (select * from BookingFee b2 where b2.RefundId=r2.Id and b2.deleted is null))
62and (exists (select * from BookingFee bf2 where bf2.bookingid=r.bookingid and bf2.type = 0 and deleted is null and not bf2.ExpenseId is null) or not exists (select * from BookingFee bf2 where bf2.bookingid=r.bookingid and bf2.type = 0 and deleted is null))
63and exists (select * from Expense where bookingid=r.bookingid and voided is null and deleted is null)
64order by r.UserId
65
66# create new fees
67insert into BookingFee (BookingId, RefundId, Description, Amount, OwnerCommissionPercent, OwnerAmount, CommissionAmount, Type, Created, ListingSiteToken)
68select r.BookingId, r.Id, 'GENERATED Refund Fee', r.w_Fee, 0, 0, 0, 0, 0, CONCAT(r.ListingSiteToken, ':payment')
69from Refund r
70where Deleted is null and voided is null and w_fee != 0 and not exists (select * from BookingFee bf where bf.RefundId=r.Id and bf.deleted is null);