Administrative and Maintenance Tools - Ad Management System

This mod needs special care when installing; please read the entire of this post and the first post in this thread before attempting an installation.

Ad Management System for Vbulletin
Requirements

• Vbulletin 3.7+
• Vbulletin 3.6.x and Additional compatibility product.
• MySQL 5.0.3+ (5.1.16 recommended)

Features

• Allow users to upload ads for display on your forums
• Manage (approve and reject) ads from the admin cp
• Payment handled via VB subscriptions

Coming soon: (either not implemented or not functional)

• Restrict max number of active ads at one time; any extras will be queued for display
• External mode (share ads between different forums)
• Default ads (show a default ad if none exist)
• Network ads (mix ads from a 3rd party host into rotation)

Installation
1. Backup DB
2a. If you have changed the name of your admincp, rename the admincp directory appropriately.
2b. Upload files in upload directory to your forum root.
2c. (Vbulletin 3.6.x only) Install the ad compatibility product
3. Import admanager product file. IMPORT 1 PRODUCT FILE ONLY (see below)
4. Edit navbar template and add a link to advertising.php

Note: there are TWO product files. One is for use with mysql versions 5.0.3+ the other will only work if:
a) Your vbulletin database user account has the SUPER privilege
b) You are running MySQL 5.1.16+ and your account has the CREATE TRIGGER privilege

If you use the first product file (for 5.0.3) you need to run the following queries using an account with the super privilege.

There is now also a third product file, this file is only needed if you are using vbulletin 3.6.x

PHP Code:

$db->query("DROP TRIGGER IF EXISTS `" . TABLE_PREFIX . "advertsinsert`;");

$db->query("DELIMITER //
CREATE TRIGGER `" . TABLE_PREFIX . "advertsinsert` AFTER INSERT ON `" . TABLE_PREFIX . "adverts`
 FOR EACH ROW BEGIN
    CASE NEW.status
        WHEN 0 THEN UPDATE " . TABLE_PREFIX . "adzones SET waiting = waiting+1 WHERE adzoneid = NEW.adzoneid;
        WHEN 1 THEN UPDATE " . TABLE_PREFIX . "adzones SET queued = queued+1 WHERE adzoneid = NEW.adzoneid;
        WHEN 2 THEN UPDATE " . TABLE_PREFIX . "adzones SET active = active+1 WHERE adzoneid = NEW.adzoneid;
        WHEN 3 THEN UPDATE " . TABLE_PREFIX . "adzones SET pending = pending+1 WHERE adzoneid = NEW.adzoneid;
        WHEN 4 THEN UPDATE " . TABLE_PREFIX . "adzones SET rejected = rejected+1 WHERE adzoneid = NEW.adzoneid;
    END CASE;
END
//
DELIMITER ;");

$db->query("DROP TRIGGER IF EXISTS " . TABLE_PREFIX . "advertsupdate`;");

$db->query("DELIMITER //
CREATE TRIGGER `" . TABLE_PREFIX . "advertsupdate` AFTER UPDATE ON `" . TABLE_PREFIX . "adverts`
 FOR EACH ROW BEGIN
    CASE NEW.status
        WHEN 0 THEN UPDATE " . TABLE_PREFIX . "adzones SET waiting = waiting+1 WHERE adzoneid = NEW.adzoneid;
        WHEN 1 THEN UPDATE " . TABLE_PREFIX . "adzones SET queued = queued+1 WHERE adzoneid = NEW.adzoneid;
        WHEN 2 THEN UPDATE " . TABLE_PREFIX . "adzones SET active = active+1 WHERE adzoneid = NEW.adzoneid;
        WHEN 3 THEN UPDATE " . TABLE_PREFIX . "adzones SET pending = pending+1 WHERE adzoneid = NEW.adzoneid;
        WHEN 4 THEN UPDATE " . TABLE_PREFIX . "adzones SET rejected = rejected+1 WHERE adzoneid = NEW.adzoneid;
    END CASE;
    CASE OLD.status
        WHEN 0 THEN UPDATE " . TABLE_PREFIX . "adzones SET waiting = waiting-1 WHERE adzoneid = OLD.adzoneid;
        WHEN 1 THEN UPDATE " . TABLE_PREFIX . "adzones SET queued = queued-1 WHERE adzoneid = OLD.adzoneid;
        WHEN 2 THEN UPDATE " . TABLE_PREFIX . "adzones SET active = active-1 WHERE adzoneid = OLD.adzoneid;
        WHEN 3 THEN UPDATE " . TABLE_PREFIX . "adzones SET pending = pending-1 WHERE adzoneid = OLD.adzoneid;
        WHEN 4 THEN UPDATE " . TABLE_PREFIX . "adzones SET rejected = rejected-1 WHERE adzoneid = OLD.adzoneid;
    END CASE;
END
//
DELIMITER ;");

$db->query("DROP TRIGGER IF EXISTS `" . TABLE_PREFIX . "advertsdelete`;");

$db->query("DELIMITER //
CREATE TRIGGER `" . TABLE_PREFIX . "advertsdelete` AFTER DELETE ON `" . TABLE_PREFIX . "adverts`
 FOR EACH ROW BEGIN
    CASE OLD.status
        WHEN 0 THEN UPDATE " . TABLE_PREFIX . "adzones SET waiting = waiting-1 WHERE adzoneid = OLD.adzoneid;
        WHEN 1 THEN UPDATE " . TABLE_PREFIX . "adzones SET queued = queued-1 WHERE adzoneid = OLD.adzoneid;
        WHEN 2 THEN UPDATE " . TABLE_PREFIX . "adzones SET active = active-1 WHERE adzoneid = OLD.adzoneid;
        WHEN 3 THEN UPDATE " . TABLE_PREFIX . "adzones SET pending = pending-1 WHERE adzoneid = OLD.adzoneid;
        WHEN 4 THEN UPDATE " . TABLE_PREFIX . "adzones SET rejected = rejected-1 WHERE adzoneid = OLD.adzoneid;
    END CASE;
END
//
DELIMITER ;"); 


Setup
This mod may be a little confusing for some; this is intended as a brief usage guide:

Once installed, a menu should be added to your admin cp (you may have to refresh first) under styles + templates, this menu contains one entry: show/edit adzones. Before you click this link, you should setup at least 1 paid subscription for use with this mod and make a note of its id (can be found by editing it, its in the title bar with the subscription title). You should use the subscriptions description to describe the ad zone it will be used for; specify max/min image dimensions and any rules you may have for ads, this description will be shown to users uploading ads to the zone.

You will be presented with a list of all ad_ template found in the database, VB3.7 has about 8, there may be more with 3.8, VB 3.6 has none, however I will be releasing a version of this mod for VB 3.6 shortly.

Click the edit link next to an ad zone you wish to allow users to buy ads in. Fill in the information; title will be the title shown to users purchasing ads so should be descriptive of the zone eg: 'Below Header'; the max active ads section dosn't work currently however will be implemented in a future release; subscription id should be the id number of the subscription you created earlier.

You will be returned to the list, now the n/a's for the adzone will have been replaced with numbers. Clicking a number will show all ads with the indicated status. Clicking the ads pending admin approval number will take you to a form where you can approve and reject ads submitted by users.

If the numbers here remain 0 even when there are ads in the categories; you havn't added the triggers correctly.

Usage
The intended usage pattern for this mod looks like this:
1. User uploads an advert to the site.
2. An admin approves the ad for display
3. The user purchases a subscription
4. The ad is displayed for the length of the subscription

This is a alpha release and may contain bugs, you should test this mod on a dev site before installing it onto a production site.

Notes on behaviours
Ads can be edited by users if and only if they have been rejected or they have not yet been approved.
Once an ad has been rejected or approved, that desision cannot be changed.
Each user may only submit 1 ad per ad zone.
This mod may conflict with any mod that adds plugins to the subscription system.

[J517E7B2F4A5]

Originally Posted by J517E7B2F4A5

this will work nice but i've just one small problem when i trying to upload and image this is what it tell me



What i've to do for fix this small error?


Edit: fixed

Founded Thank you works like a charm :up::up: