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
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
|
<?php
/**
* skrbi za odjavo posameznega uporabnik od prejemanja obvestil ankete
*
*/
class SurveyUnsubscribe {
private static $sid = null;
private static $unsubscribed = null;
function __construct($sid) {
self::$sid = $sid;
SurveyInfo::SurveyInit(self::$sid);
}
static function isUnsubscribedEmail($email) {
# če še nismo zakeširamo djavljene emaile za anketo
if (self::$unsubscribed == null && !is_array(self::$unsubscribed)) {
self::getUnsubscribedEmails();
}
if (is_array(self::$unsubscribed) && count(self::$unsubscribed) > 0 && $email != null && trim($email) != '') {
return isset(self::$unsubscribed[$email]);
}
return false;
}
static function getUnsubscribedEmails() {
# polovimo vse odjavljene e-maile in jih shranimo v array
# preverimo ali je uporabnik že odjavljen
$emails = array();
$u1s = "SELECT email FROM srv_survey_unsubscribe WHERE ank_id ='".self::$sid."'";
$u1q = sisplet_query($u1s);
while ($u1r = mysqli_fetch_assoc($u1q)) {
if (trim($u1r['email']) != '') {
$emails[$u1r['email']] = $u1r['email'];
}
}
# preverimo tabelo srv_invitations_recipients
$u2s = "SELECT email FROM srv_invitations_recipients WHERE ank_id ='".self::$sid."' AND unsubscribed = '1'";
if (count($emails) > 0 ) {
$emails_implode = implode('\', \'', $emails);
$u2s .= " AND email NOT IN ('".$emails_implode."')";
//$u2s.=" AND email NOT IN ('".implode('\',\'',$emails)."')";
}
$u2q = sisplet_query($u2s);
while ($u2r = mysqli_fetch_assoc($u2q)) {
if (trim($u2r['email']) != '') {
$emails[$u2r['email']] = $u2r['email'];
}
}
# polovimo še vse iz srv_user in users
$u3s = "SELECT user_id, email FROM srv_user WHERE ank_id ='".self::$sid."' AND unsubscribed = '1'";
$u3q = sisplet_query($u3s);
$cms_id = array();
while ($u3r = mysqli_fetch_assoc($u3q)) {
if (trim($u3r['email']) != '') {
$emails[$u3r['email']] = $u3r['email'];
} else {
#poiščemo še email v tabeli users (če gre za userja iz cms
if ((int)$u3r['user_id'] > 0) {
$cms_id[] = $u3r['user_id'];
}
}
}
if (count($cms_id) > 0) {
$u4s = "SELECT email FROM users WHERE id IN ('".implode("','",$cms_id)."')";
$u4q = sisplet_query($u4s);
while ($u4r = mysqli_fetch_assoc($u4q)) {
if (trim($u4r['email']) != '') {
$emails[$u4r['email']] = $u4r['email'];
}
}
}
self::$unsubscribed = $emails;
}
function generateCodeForEmail($email) {
#preverimo ali email že obstaja za to anketo
$used_codes = array();
$sql_string = "SELECT code FROM srv_survey_unsubscribe_codes WHERE ank_id = '".self::$sid."' AND email='".$email."'";
$sql_query = sisplet_query($sql_string);
if (mysqli_num_rows($sql_query) > 0) {
$sql_row = mysqli_fetch_assoc($sql_query);
return $sql_row['code'];
}
#polovimo katere kode smo že uporabili za to anketo
$used_codes = array();
$sql_string = "SELECT code FROM srv_survey_unsubscribe_codes WHERE ank_id = '".self::$sid."'";
$sql_query = sisplet_query($sql_string);
while ($sql_row = mysqli_fetch_assoc($sql_query)) {
$used_codes[$sql_row['code']] = $sql_row['code'];
}
# zgeneriramo kodo za upoirabika
# Izberemo random hash, ki se ni v bazi
do {
list($code,$cookie) = self::generateCode();
} while (in_array($cookie,$used_codes) && !is_numeric($cookie));
#vstavimo v tabelo srv_survey_unsubscribe_codes
$sql_insert = "INSERT INTO srv_survey_unsubscribe_codes (ank_id, email, code) VALUES ( '".self::$sid."', '".$email."', '".$cookie."') ";
$sqlQuery = sisplet_query($sql_insert);
return $cookie;
}
function generateCode() {
$cookie = md5(mt_rand(1, mt_getrandmax()) . '@' . $_SERVER['REMOTE_ADDR']);
return array(substr($cookie,0,6), $cookie);
}
function doUnsubscribe() {
global $lang;
$anketa = self::$sid;
$db_table = (SurveyInfo::getInstance()->getSurveyColumn('db_table') == 1) ? '_active' : '';
$lang_id = (int)$_GET['language'];
if ($lang_id != null) $_lang = '_'.$lang_id; else $_lang = '';
SurveySetting::getInstance()->init($anketa);
$user_bye_textA = SurveySetting::getInstance()->getSurveyMiscSetting('srvlang_user_bye_textA'.$_lang);
if ($user_bye_textA == '') $user_bye_textA = $lang['user_bye_textA'];
if (isset($_GET['uc']) && trim($_GET['uc']) != '' && isset($_GET['em']) && trim($_GET['em']) != '') {
# imamo userja iz cms, email smo poslali kodirano
#dodamo ga v tabelo: srv_survey_unsubscribe
$uc = trim($_GET['uc']);
$em = base64_decode($_GET['em']);
# preverimo obstoj in pravilnost emaila in kode v bazi
$s = "SELECT * FROM srv_survey_unsubscribe_codes WHERE ank_id='".self::$sid."' AND email='".$em."' AND code='".$uc."'";
$q = sisplet_query($s);
if (mysqli_num_rows($q) > 0) {
# zapis je v bazi uporabnika lahko odjavimo
$si = "INSERT INTO srv_survey_unsubscribe (ank_id, email, unsubscribe_time) VALUES ('".self::$sid."','".$em."',now())";
$qi = $s = sisplet_query($si);
echo $user_bye_textA;
}
else {
# zapisa ni v bazi obvestimo uporabnika o napačni kodi
echo 'Koda je napačna! Ne moremo vas odjaviti od prejemanja obvestil!';
}
exit();
}
else if ( isset($_GET['email']) && trim($_GET['email']) != '' &&
isset($_GET['uid']) && (int)trim($_GET['uid']) > 0) {
$uid = (int)trim($_GET['uid']);
$email = trim($_GET['email']);
#poiščemo userja
#poiščemo id spremenljivke z emailom
$ssp = "SELECT s.id FROM srv_spremenljivka s, srv_grupa g WHERE s.gru_id=g.id AND g.ank_id='$anketa' AND variable = 'email' and sistem ='1'";
$qsp = sisplet_query($ssp);
$rsp = mysqli_fetch_assoc($qsp);
$spid = $rsp['id'];
#poiščemo email uporabnika
if ((int)$spid > 0 && (int) $uid > 0) {
$se = "SELECT count(*) from srv_data_text".$db_table." WHERE spr_id = '$spid' AND usr_id = '$uid' AND text ='$email'";
$qe = sisplet_query($se);
list($count) = mysqli_fetch_row($qe);
}
# če ustreza email in uid, ga odjavimo
if ((int)$count > 0) {
if ($email != null && trim($email) != '') {
$si = "INSERT INTO srv_survey_unsubscribe (ank_id, email, unsubscribe_time) VALUES ('$anketa','$email',now())";
$qi = $s = sisplet_query($si);
}
$s = sisplet_query("UPDATE srv_user SET unsubscribed='1' WHERE id='$uid' AND ank_id='$anketa'");
if ($s) {
echo $user_bye_textA;
} else {
//echo mysqli_error($GLOBALS['connect_db']);
echo 'error';
}
} else {
echo 'V bazi ni podaanega emaila.';
}
}
else {
$code = strtolower( $_GET['code'] );
$msgOutputed = false;
if (trim($code) != '' && trim($anketa) != '' && (int)$anketa > 0) {
# id uporabnika v tabeli srv_user
$su = "SELECT id FROM srv_user WHERE pass='$code' AND ank_id='$anketa'";
$qu = sisplet_query($su);
$ru = mysqli_fetch_assoc($qu);
$uid = $ru['id'];
#poiščemo id spremenljivke z emailom
$ssp = "SELECT s.id FROM srv_spremenljivka s, srv_grupa g WHERE s.gru_id=g.id AND g.ank_id='$anketa' AND variable = 'email' and sistem ='1'";
$qsp = sisplet_query($ssp);
$rsp = mysqli_fetch_assoc($qsp);
$spid = $rsp['id'];
#poiščemo email uporabnika
if ((int)$spid > 0 && (int) $uid > 0) {
$se = "SELECT text as email from srv_data_text".$db_table." WHERE spr_id = '$spid' AND usr_id = '$uid'";
$qe = sisplet_query($se);
$re = mysqli_fetch_assoc($qe);
$email = $re['email'];
}
if ($email != null && trim($email) != '') {
$si = "INSERT INTO srv_survey_unsubscribe (ank_id, email, unsubscribe_time) VALUES ('$anketa','$email',now())";
$qi = $s = sisplet_query($si);
}
# preverimo ali obstaja koda za nov način pošiljanja sporočil
$sqlString = "SELECT id, email FROM srv_invitations_recipients WHERE ank_id='$anketa' AND password ='$code' AND unsubscribed='0'";
$sql_query = sisplet_query($sqlString);
if (mysqli_num_rows($sql_query) > 0 ) {
$row = mysqli_fetch_assoc($sql_query);
if (trim($row['email']) != '') {
// KAJ TO DELA TUKAJ??
//$sqlG = sisplet_query("INSERT INTO srv_glasovanje (ank_id, spr_id) VALUES ('$anketa', '$spr_id')");
$si = "INSERT INTO srv_survey_unsubscribe (ank_id, email, unsubscribe_time) VALUES ('$anketa','$row[email]',now())";
$qi = $s = sisplet_query($si);
}
$s = sisplet_query("UPDATE srv_invitations_recipients SET unsubscribed='1', date_unsubscribed=NOW() WHERE password='$code' AND ank_id='$anketa'");
sisplet_query("COMMIT");
if ($s) {
echo $user_bye_textA;
$msgOutputed = true;
} else {
//echo mysqli_error($GLOBALS['connect_db']);
echo 'error1';
}
} else {
# preverimo ali je že predhodno odjavljen
$sqlString = "SELECT id FROM srv_invitations_recipients WHERE ank_id='$anketa' AND password ='$code' AND unsubscribed='1'";
$sql_query = sisplet_query($sqlString);
if (mysqli_num_rows($sql_query) > 0 ) {
echo $lang['user_bye_textC'];
$msgOutputed = true;
} else {
#userja z kodo ni v bazi
}
}
$s = sisplet_query("UPDATE srv_user SET unsubscribed='1' WHERE (pass='$code' OR SUBSTRING(cookie,1,6) ='$code') AND ank_id='$anketa'");
if ($s) {
#tekst je bil poslan že zgoraj!
if ($msgOutputed == false) {
echo $user_bye_textA;
}
} else {
//echo mysqli_error($GLOBALS['connect_db']);
echo 'error';
}
}
}
}
}
|
