4 use base 'PciIds::DBQAny';
8 my $node = 'SELECT id, name, description, maincomment FROM locations WHERE parent = ? ORDER BY ';
9 my $noder = 'SELECT id, name, description, maincomment FROM locations WHERE parent = ? AND id LIKE ? ORDER BY ';
10 return bless PciIds::DBQAny::new( $dbh, {
11 'nodes-id' => $node.'id',
12 'nodes-name' => $node.'name',
13 'nodes-rid' => $node.'id DESC',
14 'nodes-rname' => $node.'name DESC',
15 'nodes-id-r' => $noder.'id',
16 'nodes-name-r' => $noder.'name',
17 'nodes-rid-r' => $noder.'id DESC',
18 'nodes-rname-r' => $noder.'name DESC',
19 'item' => 'SELECT parent, name, description, maincomment FROM locations WHERE id = ?',
20 'login' => 'SELECT id FROM users WHERE login = ?',
21 'email' => 'SELECT id FROM users WHERE email = ?',
22 'adduser' => 'INSERT INTO users (login, email, passwd) VALUES(?, ?, ?)',
23 'adduser-null' => 'INSERT users (email, passwd) VALUES(?, ?)',
24 'loginfomail' => 'SELECT id, passwd, logtime, lastlog FROM users WHERE email = ?',
25 'loginfoname' => 'SELECT id, passwd, logtime, lastlog, email FROM users WHERE login = ?',
26 'resetinfo' => 'SELECT id, login, passwd FROM users WHERE email = ?',
27 'changepasswd' => 'UPDATE users SET passwd = ? WHERE id = ?',
28 'setlastlog' => 'UPDATE users SET logtime = now(), lastlog = ? WHERE id = ?',
29 'rights' => 'SELECT rightId FROM rights WHERE userId = ?',
30 'newitem' => 'INSERT INTO locations (id, parent) VALUES(?, ?)',
31 'newcomment' => 'INSERT INTO history (location, owner, text, nodename, nodedescription) VALUES(?, ?, ?, ?, ?)',
32 'history' => 'SELECT history.id, history.text, history.time, history.nodename, history.nodedescription, history.seen, users.login FROM history LEFT OUTER JOIN users ON history.owner = users.id WHERE history.location = ? ORDER BY history.time',
33 'admindump' => 'SELECT
34 locations.id, locations.name, locations.description, locations.maincomment, musers.login, main.text,
35 history.id, history.text, history.nodename, history.nodedescription, users.login
37 locations INNER JOIN history ON history.location = locations.id
38 LEFT OUTER JOIN users ON history.owner = users.id
39 LEFT OUTER JOIN history AS main ON locations.maincomment = main.id
40 LEFT OUTER JOIN users AS musers ON main.owner = musers.id WHERE history.seen = "0"
41 ORDER BY locations.id, history.id
42 LIMIT 15',#Dumps all new comments with their senders and corresponding main comments and names
43 'delete-hist' => 'DELETE FROM history WHERE id = ?',
44 'mark-checked' => 'UPDATE history SET seen = 1 WHERE id = ?',
45 'delete-item' => 'DELETE FROM locations WHERE id = ?',
46 'set-maincomment' => 'UPDATE locations SET
48 name = ( SELECT nodename FROM history WHERE id = ? ),
49 description = ( SELECT nodedescription FROM history WHERE id = ? )
52 'profiledata' => 'SELECT email, xmpp, login, mailgather, xmppgather FROM users WHERE id = ?',
53 'pushprofile' => 'UPDATE users SET xmpp = ?, login = ?, mailgather = ?, xmppgather = ? WHERE id = ?',
54 'setemail' => 'UPDATE users SET email = ?, passwd = ? WHERE id = ?',
55 'notifuser' => 'SELECT location, recursive FROM notifications WHERE user = ? ORDER BY location',
56 'notifdata' => 'SELECT recursive, type, notification FROM notifications WHERE user = ? AND location = ?',
57 'drop-notif' => 'DELETE FROM notifications WHERE user = ? AND location = ?',
58 'new-notif' => 'INSERT INTO notifications (user, location, recursive, type, notification) VALUES (?, ?, ?, ?, ?)',
59 'notify' => 'INSERT INTO pending (user, comment, notification, reason) SELECT DISTINCT user, ?, ?, ? FROM notifications WHERE ( notification = 2 OR notification = ? ) AND type <= ? AND ( location = ? OR ( SUBSTR( ?, 1, LENGTH( location ) ) = location ) )',
60 'newtime-mail' => 'UPDATE users SET nextmail = FROM_UNIXTIME( UNIX_TIMESTAMP( NOW() ) + 60 * mailgather ) WHERE nextmail < NOW() AND EXISTS ( SELECT 1 FROM notifications WHERE ( notification = 0 OR notification = 2 ) AND type <= ? AND ( location = ? OR ( SUBSTR( ?, 1, LENGTH( location ) ) = location ) ) )',
61 'newtime-xmpp' => 'UPDATE users SET nextxmpp = FROM_UNIXTIME( UNIX_TIMESTAMP( NOW() ) + 60 * xmppgather ) WHERE nextxmpp < NOW() AND EXISTS ( SELECT 1 FROM notifications WHERE ( notification = 1 OR notification = 2 ) AND type <= ? AND ( location = ? OR ( SUBSTR( ?, 1, LENGTH( location ) ) = location ) ) )',
63 pending.user, users.email,
64 pending.reason, history.text, history.nodename, history.nodedescription, history.time,
65 auth.login, history.location, locations.name, locations.description
68 INNER JOIN users ON users.id = pending.user
69 INNER JOIN history ON history.id = pending.comment
70 INNER JOIN locations ON history.location = locations.id
71 INNER JOIN users AS auth ON auth.id = history.owner
73 pending.notification = 0
74 AND users.nextmail <= ?
76 pending.user, pending.reason, history.time, history.location',
78 pending.user, users.xmpp,
79 pending.reason, history.text, history.nodename, history.nodedescription, history.time,
80 auth.login, history.location, locations.name, locations.description
83 INNER JOIN users ON users.id = pending.user
84 INNER JOIN history ON history.id = pending.comment
85 INNER JOIN locations ON history.location = locations.id
86 INNER JOIN users AS auth ON auth.id = history.owner
88 pending.notification = 1
89 AND users.nextxmpp <= ?
91 pending.user, pending.reason, history.time, history.location',
92 'dropnotifsxmpp' => 'DELETE FROM pending WHERE notification = 1 AND EXISTS ( SELECT 1 FROM users WHERE users.id = pending.user AND nextxmpp <= ? )',
93 'dropnotifsmail' => 'DELETE FROM pending WHERE notification = 0 AND EXISTS ( SELECT 1 FROM users WHERE users.id = pending.user AND nextmail <= ? )',
94 'time' => 'SELECT NOW()'
99 my %sorts = ( 'id' => 1, 'rid' => 1, 'name' => 1, 'rname' => 1 );
102 my( $self, $parent, $args, $restrict ) = @_;
104 $q = $args->{'sort'} if( defined( $args->{'sort'} ) && defined( $sorts{$args->{'sort'}} ) );
105 if( defined( $restrict ) && ( $restrict ne "" ) ) {
106 return $self->query( 'nodes-'.$q.'-r', [ $parent, $parent.'/'.$restrict.'%' ] );
108 return $self->query( 'nodes-'.$q, [ $parent ] );
113 my( $self, $id ) = @_;
114 my $result = $self->query( "item", [ $id ] );
115 if( scalar @{$result} ) {
116 return $result->[ 0 ];
123 my( $self, $login ) = @_;
124 my $result = $self->query( 'login', [ $login ] );
125 return scalar @{$result};
129 my( $self, $email ) = @_;
130 my $result = $self->query( 'email', [ $email ] );
131 return scalar @{$result};
134 sub addUser( $$$$ ) {
135 my( $self, $login, $email, $passwd ) = @_;
137 if( ( defined $login ) && ( $login ne '' ) ) {
138 $self->command( 'adduser', [ $login, $email, $passwd ] );
140 $self->command( 'adduser-null', [ $email, $passwd ] );
146 return $self->last();
150 sub getLogInfo( $$ ) {
151 my( $self, $info ) = @_;
153 if( $info =~ /@/ ) {#An email address
154 $data = $self->query( 'loginfomail', [ $info ] );
156 $data = $self->query( 'loginfoname', [ $info ] );
158 if( scalar @{$data} ) {
159 my( $id, $passwd, $logtime, $lastlog, $email ) = @{$data->[ 0 ]};
161 $logstring = "Last logged from $lastlog at $logtime" if( defined $logtime && defined $lastlog );
162 $email = $info if( $info =~ /@/ );
163 return( $id, $passwd, $email, $logstring );
170 my( $self, $id ) = @_;
171 return $self->query( 'rights', [ $id ] );
174 sub setLastLog( $$$ ) {
175 my( $self, $id, $from ) = @_;
176 $self->command( 'setlastlog', [ $from, $id ] );
180 my( $self, $addr ) = @_;
181 return $self->query( 'history', [ $addr ] );
184 sub submitItem( $$$ ) {
185 my( $self, $data, $auth ) = @_;
186 my( $addr ) = ( $data->{'address'} );
187 return( 'exists', undef ) if( defined( $self->item( $addr->get(), 0 ) ) );
189 $self->command( 'newitem', [ $addr->get(), $addr->parent()->get() ] );
190 $self->command( 'newcomment', [ $addr->get(), $auth->{'authid'}, $data->{'text'}, $data->{'name'}, $data->{'description'} ] );
195 return( 'internal: '.$@, undef );
197 return( '', $self->last() );
200 sub submitComment( $$$$ ) {
201 my( $self, $data, $auth, $address ) = @_;
202 $self->command( 'newcomment', [ $address->get(), $auth->{'authid'}, $data->{'text'}, $data->{'name'}, $data->{'description'} ], 1 );
203 return $self->last();
207 return shift->query( 'admindump', [] );
210 sub deleteHistory( $$ ) {
211 my( $self, $id ) = @_;
212 $self->command( 'delete-hist', [ $id ] );
215 sub markChecked( $$ ) {
216 my( $self, $id ) = @_;
217 $self->command( 'mark-checked', [ $id ] );
220 sub deleteItem( $$ ) {
221 my( $self, $id ) = @_;
222 $self->command( 'delete-item', [ $id ] );
225 sub setMainComment( $$$ ) {
226 my( $self, $location, $comment ) = @_;
227 $self->command( 'set-maincomment', [ $comment, $comment, $comment, $location ] );
230 sub resetInfo( $$ ) {
231 my( $self, $mail ) = @_;
232 my $result = $self->query( 'resetinfo', [ $mail ] );
233 if( scalar @{$result} ) {
234 return ( @{$result->[0]} );
240 sub changePasswd( $$$ ) {
241 my( $self, $id, $passwd ) = @_;
242 $self->command( 'changepasswd', [ $passwd, $id ] );
245 sub profileData( $$ ) {
246 my( $self, $id ) = @_;
248 ( $result{'email'}, $result{'xmpp'}, $result{'login'}, $result{'email_time'}, $result{'xmpp_time'} ) = @{$self->query( 'profiledata', [ $id ] )->[0]};
252 sub setEmail( $$$$ ) {
253 my( $self, $id, $email, $passwd ) = @_;
254 $self->command( 'setemail', [ $email, $passwd, $id ] );
257 sub pushProfile( $$$$$$ ) {
258 my( $self, $id, $login, $xmpp, $mailgather, $xmppgather ) = @_;
259 $self->command( 'pushprofile', [ $xmpp, $login, $mailgather, $xmppgather, $id ] );
262 sub notificationsUser( $$ ) {
263 my( $self, $uid ) = @_;
264 return $self->query( 'notifuser', [ $uid ] );
267 sub getNotifData( $$$ ) {
268 my( $self, $uid, $location ) = @_;
269 my $result = $self->query( 'notifdata', [ $uid, $location ] );
271 my( $recursive, $notification, $way ) = @{$result->[0]};
273 'recursive' => $recursive,
274 'notification' => $notification,
277 return { 'recursive' => 1 };
281 sub submitNotification( $$$$ ) {
282 my( $self, $uid, $location, $data ) = @_;
283 $self->command( 'drop-notif', [ $uid, $location ] );
284 $self->command( 'new-notif', [ $uid, $location, $data->{'recursive'}, $data->{'notification'}, $data->{'way'} ] ) unless( $data->{'notification'} == 3 );
287 sub pushNotifications( $$$$$ ) {
288 my( $self, $location, $comment, $priority, $reason ) = @_;
289 $self->command( 'notify', [ $comment, 0, $reason, 0, $priority, $location, $location ] );
290 $self->command( 'notify', [ $comment, 1, $reason, 1, $priority, $location, $location ] );
291 $self->command( 'newtime-mail', [ $priority, $location, $location ] );
292 $self->command( 'newtime-xmpp', [ $priority, $location, $location ] );
295 sub mailNotifs( $$ ) {
296 my( $self, $time ) = @_;
297 return $self->query( 'mailout', [ $time ] );
300 sub xmppNotifs( $$ ) {
301 my( $self, $time ) = @_;
302 return $self->query( 'xmppout', [ $time ] );
307 return $self->query( 'time', [] )->[0]->[0];
310 sub dropNotifs( $$ ) {
311 my( $self, $time ) = @_;
312 $self->command( 'dropnotifsmail', [ $time ] );
313 $self->command( 'dropnotifsxmpp', [ $time ] );