Changeset 1311 in ExiteCMS


Ignore:
Timestamp:
02/27/08 11:25:49 (4 years ago)
Author:
hverton
Message:

added the new thread_first_read field to all posts unread queries, to get a more accurante unread posts number

Location:
trunk
Files:
7 edited

Legend:

Unmodified
Added
Removed

  • trunk/forum/index.php

    r1307 r1311  
    8181    if (iMEMBER) { 
    8282        if ($userdata['user_posts_unread']) { 
    83             // include the users own posts 
    84             $result2 = dbquery("SELECT count(*) as unread FROM ".$db_prefix."posts p LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id WHERE tr.user_id = '".$userdata['user_id']."' AND tr.forum_id = '".$data['forum_id']."' AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") AND (p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read)", false); 
     83            $result = dbquery(" 
     84                SELECT count(*) as unread  
     85                    FROM ".$db_prefix."posts p  
     86                        LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id  
     87                    WHERE tr.user_id = '".$userdata['user_id']."'  
     88                        AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") 
     89                        AND ((p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read) 
     90                            OR (p.post_datestamp < tr.thread_first_read OR (p.post_edittime != 0 AND p.post_edittime < tr.thread_first_read)))" 
     91                ); 
    8592        } else { 
    86             // filter the users own posts 
    87             $result2 = dbquery("SELECT count(*) as unread FROM ".$db_prefix."posts p LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id WHERE tr.user_id = '".$userdata['user_id']."' AND tr.forum_id = '".$data['forum_id']."' AND p.post_author != '".$userdata['user_id']."' AND p.post_edituser != '".$userdata['user_id']."' AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") AND (p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read)", false); 
    88         } 
     93            $result = dbquery(" 
     94                SELECT count(*) as unread  
     95                    FROM ".$db_prefix."posts p  
     96                        LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id  
     97                    WHERE tr.user_id = '".$userdata['user_id']."'  
     98                        AND p.post_author != '".$userdata['user_id']."' 
     99                        AND p.post_edituser != '".$userdata['user_id']."' 
     100                        AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") 
     101                        AND ((p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read) 
     102                            OR (p.post_datestamp < tr.thread_first_read OR (p.post_edittime != 0 AND p.post_edittime < tr.thread_first_read)))" 
     103                ); 
     104        }  
    89105        $data['unread_posts'] = ($result2 ? mysql_result($result2, 0) : 0); 
    90106    } else { 

  • trunk/forum/options.php

    r1247 r1311  
    243243        $result = dbquery("SELECT user_id, thread_last_read FROM ".$db_prefix."threads_read WHERE thread_id = '".$thread_id."'"); 
    244244        while ($data = dbarray($result)) { 
     245            $result2 = dbquery("UPDATE ".$db_prefix."threads_read SET thread_first_read = '".$data['thread_first_read']."' WHERE user_id = '".$data['user_id']."' AND thread_id = '".$new_thread_id."' AND thread_first_read > '".$data['thread_first_read']."'"); 
    245246            $result2 = dbquery("UPDATE ".$db_prefix."threads_read SET thread_last_read = '".$data['thread_last_read']."' WHERE user_id = '".$data['user_id']."' AND thread_id = '".$new_thread_id."' AND thread_last_read < '".$data['thread_last_read']."'"); 
    246247        } 

  • trunk/forum/viewforum.php

    r1307 r1311  
    9999    // update the last_read datestamp of all threads found 
    100100    while ($data = dbarray($result)) { 
    101         $result2 = dbquery("UPDATE ".$db_prefix."threads_read SET thread_last_read = '".time()."' WHERE user_id = '".$userdata['user_id']."' AND thread_id = '".$data['thread_id']."'"); 
    102     } 
    103 } 
    104  
    105 // get the number of unread posts in this forum 
     101        $result2 = dbquery("UPDATE ".$db_prefix."threads_read SET thread_first_read = '0', thread_last_read = '".time()."' WHERE user_id = '".$userdata['user_id']."' AND thread_id = '".$data['thread_id']."'"); 
     102    } 
     103} 
     104 
     105// get the number of unread posts for this user in this forum 
    106106if (iMEMBER) { 
    107107    if ($userdata['user_posts_unread']) { 
    108         // include the users own posts 
    109         $result = dbquery("SELECT count(*) as unread FROM ".$db_prefix."posts p LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id WHERE tr.user_id = '".$userdata['user_id']."' AND tr.forum_id = '".$forum_id."' AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") AND (p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read)", false); 
    110     } else { 
    111         // filter the users own posts 
    112         $result = dbquery("SELECT count(*) as unread FROM ".$db_prefix."posts p LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id WHERE tr.user_id = '".$userdata['user_id']."' AND tr.forum_id = '".$forum_id."' AND p.post_author != '".$userdata['user_id']."' AND p.post_edituser != '".$userdata['user_id']."' AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") AND (p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read)", false); 
    113     } 
     108        $result = dbquery(" 
     109            SELECT count(*) as unread  
     110                FROM ".$db_prefix."posts p  
     111                    LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id  
     112                WHERE tr.user_id = '".$userdata['user_id']."'  
     113                    AND tr.forum_id = '".$forum_id."' 
     114                    AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") 
     115                    AND ((p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read) 
     116                        OR (p.post_datestamp < tr.thread_first_read OR (p.post_edittime != 0 AND p.post_edittime < tr.thread_first_read)))" 
     117            ); 
     118    } else { 
     119        $result = dbquery(" 
     120            SELECT count(*) as unread  
     121                FROM ".$db_prefix."posts p  
     122                    LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id  
     123                WHERE tr.user_id = '".$userdata['user_id']."'  
     124                    AND tr.forum_id = '".$forum_id."' 
     125                    AND p.post_author != '".$userdata['user_id']."' 
     126                    AND p.post_edituser != '".$userdata['user_id']."' 
     127                    AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") 
     128                    AND ((p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read) 
     129                        OR (p.post_datestamp < tr.thread_first_read OR (p.post_edittime != 0 AND p.post_edittime < tr.thread_first_read)))" 
     130            ); 
     131    }  
    114132    $variables['unread_posts'] = ($result ? mysql_result($result, 0) : 0); 
    115133} else { 

  • trunk/includes/theme_functions.php

    r1284 r1311  
    463463            $result = dbquery("UPDATE ".$db_prefix."users SET user_status = 1, user_ban_reason = '', user_ban_expire = '".time()."' WHERE user_bad_email > 0 AND user_bad_email < '".(time() - $day * 90)."'"); 
    464464        } 
    465         // read threads indicators: use the defined threshold (available since v7.0 rev.1190) 
    466         if ($settings['revision'] >= 1190) { 
     465        // read threads indicators: use the defined threshold (available since v7.0 rev.1193) 
     466        if ($settings['revision'] >= 1193) { 
    467467            $result = dbquery("DELETE FROM ".$db_prefix."threads_read WHERE thread_last_read < '".$settings['unread_threshold']."'", false); 
    468468        } 

  • trunk/modules/user_info_panel/user_info_panel.php

    r1307 r1311  
    1818$variables = array(); 
    1919 
    20 if (isset($userdata['user_id'])) { 
     20if (iMEMBER) { 
    2121    // store the user's ID and name 
    2222    $variables['user_id'] = isset($userdata['user_id'])?$userdata['user_id']:0; 
    … …  
    3131        $variables['adminpage5'] = checkrights("T"); 
    3232    } 
     33    // new PM messages 
    3334    $variables['new_pm_msg'] = dbcount("(pmindex_id)", "pm_index", "pmindex_user_id ='".$userdata['user_id']."' AND pmindex_to_id='".$userdata['user_id']."' AND pmindex_read_datestamp = '0'"); 
     35    // new forum messages 
    3436    if ($userdata['user_posts_unread']) { 
    35         // include the users own posts 
    36         $result = dbquery("SELECT count(*) as unread FROM ".$db_prefix."posts p LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id WHERE tr.user_id = '".$userdata['user_id']."' AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") AND (p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read)", false); 
     37        $result = dbquery(" 
     38            SELECT count(*) as unread  
     39                FROM ".$db_prefix."posts p  
     40                    LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id  
     41                WHERE tr.user_id = '".$userdata['user_id']."'  
     42                    AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") 
     43                    AND ((p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read) 
     44                        OR (p.post_datestamp < tr.thread_first_read OR (p.post_edittime != 0 AND p.post_edittime < tr.thread_first_read)))" 
     45            ); 
    3746    } else { 
    38         // filter the users own posts 
    39         $result = dbquery("SELECT count(*) as unread FROM ".$db_prefix."posts p LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id WHERE tr.user_id = '".$userdata['user_id']."' AND p.post_author != '".$userdata['user_id']."' AND p.post_edituser != '".$userdata['user_id']."' AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") AND (p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read)", false); 
    40     } 
     47        $result = dbquery(" 
     48            SELECT count(*) as unread  
     49                FROM ".$db_prefix."posts p  
     50                    LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id  
     51                WHERE tr.user_id = '".$userdata['user_id']."'  
     52                    AND p.post_author != '".$userdata['user_id']."' 
     53                    AND p.post_edituser != '".$userdata['user_id']."' 
     54                    AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") 
     55                    AND ((p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read) 
     56                        OR (p.post_datestamp < tr.thread_first_read OR (p.post_edittime != 0 AND p.post_edittime < tr.thread_first_read)))" 
     57            ); 
     58    }  
    4159    $variables['new_post_msg'] = ($result ? mysql_result($result, 0) : 0); 
    4260    // check if the forum_threads_list_panel module is installed 

  • trunk/profile.php

    r1307 r1311  
    9292    $data['user_posts'] = number_format($data['user_posts']); 
    9393    $data['show_viewposts_button'] = ($data['user_posts'] > 0 and file_exists(PATH_MODULES."forum_threads_list_panel/my_posts.php")); 
    94     $result = dbquery("SELECT count(*) as unread FROM ".$db_prefix."posts p LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id WHERE tr.user_id = '".$data['user_id']."' AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") AND (p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read)", false); 
     94    if ($data['user_posts_unread']) { 
     95        $result = dbquery(" 
     96            SELECT count(*) as unread  
     97                FROM ".$db_prefix."posts p  
     98                    LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id  
     99                WHERE tr.user_id = '".$data['user_id']."'  
     100                    AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") 
     101                    AND ((p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read) 
     102                        OR (p.post_datestamp < tr.thread_first_read OR (p.post_edittime != 0 AND p.post_edittime < tr.thread_first_read)))" 
     103            , false); 
     104    } else { 
     105        $result = dbquery(" 
     106            SELECT count(*) as unread  
     107                FROM ".$db_prefix."posts p  
     108                    LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id  
     109                WHERE tr.user_id = '".$data['user_id']."'  
     110                    AND p.post_author != '".$data['user_id']."' 
     111                    AND p.post_edituser != '".$data['user_id']."' 
     112                    AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") 
     113                    AND ((p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read) 
     114                        OR (p.post_datestamp < tr.thread_first_read OR (p.post_edittime != 0 AND p.post_edittime < tr.thread_first_read)))" 
     115            , false); 
     116    }  
    95117    $data['unread_count'] = ($result ? mysql_result($result, 0) : 0); 
    96118    $data['user_email'] = str_replace("@","&#64;",$data['user_email']); 

  • trunk/themes/ExiteCMS/theme.php

    r1307 r1311  
    6767if (iMEMBER) { 
    6868    if ($userdata['user_posts_unread']) { 
    69         // include the users own posts 
    70         $result = dbquery("SELECT count(*) as unread FROM ".$db_prefix."posts p LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id WHERE tr.user_id = '".$userdata['user_id']."' AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") AND (p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read)", false); 
     69        $result = dbquery(" 
     70            SELECT count(*) as unread  
     71                FROM ".$db_prefix."posts p  
     72                    LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id  
     73                WHERE tr.user_id = '".$userdata['user_id']."'  
     74                    AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") 
     75                    AND ((p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read) 
     76                        OR (p.post_datestamp < tr.thread_first_read OR (p.post_edittime != 0 AND p.post_edittime < tr.thread_first_read)))" 
     77            ); 
    7178    } else { 
    72         // filter the users own posts 
    73         $result = dbquery("SELECT count(*) as unread FROM ".$db_prefix."posts p LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id WHERE tr.user_id = '".$userdata['user_id']."' AND p.post_author != '".$userdata['user_id']."' AND p.post_edituser != '".$userdata['user_id']."' AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") AND (p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read)", false); 
    74     } 
     79        $result = dbquery(" 
     80            SELECT count(*) as unread  
     81                FROM ".$db_prefix."posts p  
     82                    LEFT JOIN ".$db_prefix."threads_read tr ON p.thread_id = tr.thread_id  
     83                WHERE tr.user_id = '".$userdata['user_id']."'  
     84                    AND p.post_author != '".$userdata['user_id']."' 
     85                    AND p.post_edituser != '".$userdata['user_id']."' 
     86                    AND (p.post_datestamp > ".$settings['unread_threshold']." OR p.post_edittime > ".$settings['unread_threshold'].") 
     87                    AND ((p.post_datestamp > tr.thread_last_read OR p.post_edittime > tr.thread_last_read) 
     88                        OR (p.post_datestamp < tr.thread_first_read OR (p.post_edittime != 0 AND p.post_edittime < tr.thread_first_read)))" 
     89            ); 
     90    }  
    7591    $variables['new_posts'] = ($result ? mysql_result($result, 0) : 0); 
    7692} else { 
Note: See TracChangeset for help on using the changeset viewer.