| | 46 | |
| | 47 | DECLARE @regularIssueDate DATETIME |
| | 48 | SET @regularIssueDate = '2013-12-24 17:00' |
| | 49 | |
| | 50 | DECLARE @changeIssueDate DATETIME |
| | 51 | SET @changeIssueDate = '2013-12-20 16:00' |
| | 52 | |
| | 53 | declare @ads as table (adid integer) |
| | 54 | declare @ads2 as table (adid integer, orderitemId integer) |
| | 55 | |
| | 56 | /*Ogłoszenia które normalnie wygasną przed 17:00 we wtorek ale mają przedłużenia - one powinny pójść*/ |
| | 57 | insert into @ads |
| | 58 | select productid from orderitems i join orders o on o.id = i.orderid where productid in |
| | 59 | ( |
| | 60 | select id from ads where enddate > @changeIssueDate and enddate < @regularIssueDate and status = 1 |
| | 61 | ) and i.startdate >= @changeIssueDate and i.startdate < @regularIssueDate and i.magazinepromotion > 0 and o.status <> 4 |
| | 62 | |
| | 63 | /*Ogłoszenia które maja start miedzy zmieniona data wydania a stara 17:00 we wtorek - one powinny pójść*/ |
| | 64 | insert into @ads2 |
| | 65 | select productid, i.id from orderitems i join orders o on o.id = i.orderid |
| | 66 | where o.status <> 4 and i.startdate >= @changeIssueDate and startdate < @regularIssueDate |
| | 67 | and subscriptionpromotion is null and i.magazinepromotion > 0 |
| | 68 | |
| | 69 | select * from @ads |
| | 70 | select * from @ads2 |
| | 71 | |
| | 72 | /* ogloszenia ktore trzeba wstrzymac - do exportu */ |
| | 73 | /*Ogłoszenia które normalnie wygasną przed 17:00, we wtorek - one nie powinny iść do tego poniedziałkowego wydania*/ |
| | 74 | select currentorderitem as oi, customerid as cid , * from ads where enddate > @changeIssueDate and enddate < @regularIssueDate and status = 1 and magazinepromotion > 0 |
| | 75 | and (id not in (select adid from @ads) or id not in (select adid from @ads2)) order by currentorderitem desc |
| | 76 | |
| | 77 | /* wstzrymanie przed 17 - aktywacja ogloszen po exporcie */ |
| | 78 | /*edycja ogloszen - wstrzymanie ogloszen ktore wygasna przed stara data wydania - wykluczajac przedluzenia i te rozpoczynajace sie miedzy pon a wtorkiem */ |
| | 79 | update ads set status=5 where enddate > @changeIssueDate and enddate < @regularIssueDate and status = 1 and magazinepromotion > 0 |
| | 80 | and (id not in (select adid from @ads) or id not in (select adid from @ads2)) |
| | 81 | |
| | 82 | select * from ads where status=5 |
| | 83 | |
| | 84 | update ads set status=1 where status=5 |
