My favorites
▼
|
Sign in
pyodbc
Python ODBC library
Project Home
Downloads
Wiki
READ-ONLY: This project has been
archived
. For more information see
this post
.
Search
Search within:
All issues
Open issues
New issues
Issues to verify
for
Advanced search
Search tips
Subscriptions
Issue
73
attachment: campaign_files_expiry.sql
(7.2 KB)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
/*
* Used to select the campaign files and the expiry time for them for a given
* device
*/
SELECT
"BaseDevice"."Blacklisted",
"BaseDevice"."CampaignsActive",
"ActiveCampaigns"."CampaignID",
"ActiveCampaigns"."CampaignName",
"ActiveCampaigns"."CampaignCupon" -- campaign generates cupons --
AND
(
"ActiveCampaigns"."CuponProgress" IS NULL
OR
"ActiveCampaigns"."CuponCapacity" IS NULL
OR
"ActiveCampaigns"."CuponCapacity" = 0
OR
"ActiveCampaigns"."CuponCapacity" > "ActiveCampaigns"."CuponProgress" -- cupons still fit campaign's capacity --
)
AND
(
"CampaignDeviceCupons"."Cupons" IS NULL
OR
"ActiveCampaigns"."CuponPersistence" IS NULL
OR
"ActiveCampaigns"."CuponPersistence" = 0
OR
"CampaignDeviceCupons"."Cupons" < "ActiveCampaigns"."CuponPersistence" -- device can still receive cupons on this campaign --
)
AND
(
SELECT Count(*)
FROM "CampaignHourCupons"
WHERE
"CampaignHourCupons"."CampaignID" = "ActiveCampaigns"."CampaignID"
AND
"CampaignHourCupons"."CampaignCuponTime" > CURRENT_TIMESTAMP - INTERVAL '01:00:00.0' HOUR TO SECOND
)
<
"ActiveCampaigns"."CuponRate" -- campaign cupons are sent rarely enough --
AS "GenerateCupon",
"ActiveCampaigns"."CuponFormat",
"ActiveCampaigns"."SourceName",
CAST("ActiveCampaigns"."PriorityCampaign" AS CHARACTER VARYING(255)) AS "PriorityCampaign",
CAST("ActiveCampaigns"."CampaignPeriod" AS CHARACTER VARYING(255)) AS "CampaignPeriod",
CURRENT_TIMESTAMP + "ActiveCampaigns"."CampaignPeriod" * INTERVAL '1 00:00:00' DAY AS "NominalExpiryTime",
CAST("ActiveCampaigns"."Resilience" AS CHARACTER VARYING(255)) AS "Resilience",
"ActiveCampaigns"."DefaultMedia",
"CampaignFiles"."CampaignMediaID",
"CampaignFiles"."CampaignMedia",
POSITION
(
'true' IN
-- concatenate in a string the timestamp with transfer status and get the maximum --
MAX(CAST("CampaignLog"."CurrentTimeStamp" AS CHARACTER VARYING(255)) || ' ' || "CampaignLog"."Success")
)
>
0 -- check the status found concatenated with --
-- the resulting maximum time stamp --
AS "FileTransferSuccessfull",
CASE
POSITION
(
'true' IN
-- concatenate in a string the timestamp with transfer status and get the maximum --
MAX(CAST("CampaignLog"."CurrentTimeStamp" AS CHARACTER VARYING(255)) || ' ' || "CampaignLog"."Success")
)
>
0
WHEN
TRUE
THEN
MAX("CampaignLog"."CurrentTimeStamp")
+
"ActiveCampaigns"."CampaignPeriod" * INTERVAL '1 00:00:00' DAY
ELSE
CASE
(
SELECT
COUNT(NULLIF("LocalMediaCampaignLog"."Success", TRUE)) AS "ErrorsCount"
FROM
(
SELECT
*
FROM
"CampaignLog" AS "LocalCampaignLog"
WHERE
"LocalCampaignLog"."DeviceAddress" = "BaseDevice"."Address"
AND
"LocalCampaignLog"."CampaignID" = "ActiveCampaigns"."CampaignID"
AND
"LocalCampaignLog"."CampaignMediaID" = "CampaignFiles"."CampaignMediaID"
ORDER BY
"LocalCampaignLog"."CurrentTimeStamp"
LIMIT
"ActiveCampaigns"."Resilience"
)
AS "LocalMediaCampaignLog"
)
WHEN
"ActiveCampaigns"."Resilience"
THEN
MAX("CampaignLog"."CurrentTimeStamp")
+
"ActiveCampaigns"."CampaignPeriod" * INTERVAL '1 00:00:00' DAY
ELSE
NULL -- few errors, should try sending the file again --
END
END
AS "FileExpiryTime",
MAX("CampaignLog"."CurrentTimeStamp") AS "LastTransferTime"
FROM
(
SELECT
"DeviceAddress" AS "Address",
"Blacklisted",
"Blacklisted" AND EXISTS(SELECT "CampaignID" FROM "ActiveCampaigns" LIMIT 1)
AS "CampaignsActive"
FROM
(
SELECT
"DeviceAddress",
"DeviceAddress" IN (SELECT "BluetoothAddress" FROM "BlackList") AS "Blacklisted"
FROM
(
SELECT CAST(? AS DECIMAL(15)) AS "DeviceAddress" FROM INFORMATION_SCHEMA.TABLES LIMIT 1
)
AS "BaseDeviceAddress"
)
AS "BaseDeviceBlacklist"
)
AS "BaseDevice"
LEFT OUTER JOIN
"ActiveCampaigns" ON "BaseDevice"."Blacklisted" IS FALSE
LEFT OUTER JOIN
"CampaignDeviceCupons" ON
"CampaignDeviceCupons"."CampaignID" = "ActiveCampaigns"."CampaignID"
AND
"CampaignDeviceCupons"."BluetoothAddress" = "BaseDevice"."Address"
LEFT OUTER JOIN
(
SELECT
"CampaignID",
"CampaignMediaID",
"CampaignMedia"
FROM
"CampaignsMedia"
UNION
SELECT
"CampaignID",
TIMESTAMP '1970-01-01 00:00:00.000000' AS "CampaignMediaID",
"DefaultMedia" AS "CampaignMedia"
FROM
"Campaigns"
)
AS "CampaignFiles" USING ("CampaignID")
LEFT OUTER JOIN "CampaignLog"
ON
("BaseDevice"."Blacklisted" IS FALSE)
AND
("BaseDevice"."Address" = "CampaignLog"."DeviceAddress")
AND
("CampaignFiles"."CampaignID" = "CampaignLog"."CampaignID")
AND
("CampaignFiles"."CampaignMediaID" = "CampaignLog"."CampaignMediaID")
AND
("CampaignLog"."CurrentTimeStamp" > (CURRENT_TIMESTAMP - "ActiveCampaigns"."CampaignPeriod" * INTERVAL '1 00:00:00' DAY))
GROUP BY
"BaseDevice"."Blacklisted",
"BaseDevice"."CampaignsActive",
"BaseDevice"."Address",
"ActiveCampaigns"."PriorityCampaign", -- campaigns selected in the order of priority --
"ActiveCampaigns"."CampaignID",
"ActiveCampaigns"."CampaignName",
"ActiveCampaigns"."CampaignCupon",
"ActiveCampaigns"."CuponCapacity",
"ActiveCampaigns"."CuponPersistence",
"ActiveCampaigns"."CuponRate",
"ActiveCampaigns"."CuponFormat",
"ActiveCampaigns"."SourceName",
"ActiveCampaigns"."CampaignPeriod",
"ActiveCampaigns"."Resilience",
"ActiveCampaigns"."DefaultMedia",
"CampaignDeviceCupons"."Cupons",
"CampaignFiles"."CampaignMediaID", -- files delivered in the creation order (CampaignMediaID) --
"CampaignFiles"."CampaignMedia"
ORDER BY
"ActiveCampaigns"."PriorityCampaign" DESC, -- campaigns selected in the order of priority --
"ActiveCampaigns"."CampaignID" ASC, -- otherwise selected in creation order --
"CampaignFiles"."CampaignMediaID" ASC -- files delivered in the creation order (CampaignMediaID) --
Powered by
Google Project Hosting