<?php
/* Jamroom mcClassifiedAds Module schema file
* @copyright 2009 by MQN Productions
* @author Tim McCune
timm@music-creatures.com
* @Purpose To Verify/Build mySQL Tables and populate sample data for mcClassifiedAds Module.
*/
defined('IN_JAMROOM') or exit();
// Create mcAds Table
$tbl = "CREATE TABLE {$jamroom_db['mcAds']} (
ad_id INT(7) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
ad_band_id INT(7) UNSIGNED NOT NULL,
ad_title VARCHAR(75) NOT NULL DEFAULT 'Ad Title',
ad_content TEXT NOT NULL,
ad_start_date VARCHAR(30) NOT NULL DEFAULT '%m/%d/%y',
ad_category_id SMALLINT(7) UNSIGNED NOT NULL,
ad_city VARCHAR(30) NOT NULL DEFAULT '',
ad_location VARCHAR(75) NOT NULL DEFAULT '',
ad_zipcode VARCHAR(10) NOT NULL DEFAULT '',
ad_visibility TINYINT(1) NOT NULL DEFAULT '1',
ad_status TINYINT(1) NOT NULL DEFAULT '0',
ad_approval_date VARCHAR(30),
ad_created VARCHAR(30) NOT NULL DEFAULT '%m/%d/%y %H:%M:%S',
ad_modified VARCHAR(30),
ad_views INT(7) UNSIGNED NOT NULL DEFAULT '0',
ad_deleteflag TINYINT(1) DEFAULT '0',
ad_image_time INT(11) UNSIGNED NOT NULL DEFAULT '0',
ad_image_name VARCHAR(255) NOT NULL DEFAULT '',
ad_image_type VARCHAR(30) NOT NULL DEFAULT '',
ad_image_size INT(10) NOT NULL DEFAULT '0',
ad_image_height INT(4) UNSIGNED NOT NULL DEFAULT '0',
ad_image_width INT(4) UNSIGNED NOT NULL DEFAULT '0',
ad_image_extension VARCHAR(5) NOT NULL DEFAULT '0'
)";
// Verify mcAds Table
dbVerifyTable($jamroom_db['mcAds'],$tbl);
// Create mcCategories Table
$tbl = "CREATE TABLE {$jamroom_db['mcCategories']} (
category_id INT(7) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
category_band_id INT(7) UNSIGNED NOT NULL,
category_title VARCHAR(75) NOT NULL DEFAULT '',
category_description VARCHAR(255) NOT NULL DEFAULT '',
category_parent INT(7) NOT NULL DEFAULT '0',
category_status TINYINT(1) NOT NULL DEFAULT '0',
category_created VARCHAR(30) NOT NULL DEFAULT '%m/%d/%y %H:%M:%S',
category_modified VARCHAR(30),
category_isPremium CHAR(3) NOT NULL DEFAULT 'no',
category_deleteflag TINYINT(1) DEFAULT '0',
category_image_time INT(11) UNSIGNED NOT NULL DEFAULT '0',
category_image_name VARCHAR(255) NOT NULL DEFAULT '',
category_image_type VARCHAR(30) NOT NULL DEFAULT '',
category_image_size INT(10) NOT NULL DEFAULT '0',
category_image_height INT(4) UNSIGNED NOT NULL DEFAULT '0',
category_image_width INT(4) UNSIGNED NOT NULL DEFAULT '0',
category_image_extension VARCHAR(5) NOT NULL DEFAULT '0'
)";
// Verify mcCategories Table
dbVerifyTable($jamroom_db['mcCategories'],$tbl);
// Check mCategories for content. If Empty, Populate with sample data
$req = "SELECT COUNT(category_id) AS catcount
FROM {$jamroom_db['mcCategories']}";
$_rt = dbQuery($req,'SINGLE');
if (!checkType($_rt['catcount'],'number_nz')) {
// Build Query to populate mcCategories Table with sample data
$catreq = "INSERT INTO {$jamroom_db['mcCategories']} (
category_id, category_title, category_description, category_status, category_parent, category_created
) VALUES
(1,'For Sale', 'Musical Items for sale. IE: Musical Instruments, Recording Equipment, Memorabilia, Music Merchandise.','1','0','".date('m/d/y G:i:s')."'),
(2,'Looking For Items', 'Looking for items. IE: Musical Instruments, Recording Equipment, Memorabilia, Music Merchandise.','1','0','".date('m/d/y G:i:s')."'),
(3,'Musicians Wanted', 'Band or musical groups, looking for new or replacement members.','1','0','".date('m/d/y G:i:s')."'),
(4,'Musicians Looking', 'Musicians looking for Band or other musicians.','1','0','".date('m/d/y G:i:s')."'),
(5,'Professional Services', 'Recording Studio, Photographer, Agent, Promoter, etc.','1','0','".date('m/d/y G:i:s')."'),
(6,'Misc', 'Any music related ads that do not fit any other category.','1','0','".date('m/d/y G:i:s')."')";
// Run $catreq Query
dbQuery($catreq);
}
$req = "UPDATE {$jamroom_db['mcCategories']} SET category_isPremium='no' WHERE category_isPremium=''";
dbQuery($req);
# ########################################################################################################################
# ### mcComplaints table
# ### Purpose: This table will hold all classified ads abuse reports, submitted by users.
# ########################################################################################################################
# complaint_id - This is the unique ID for all complaints.
# complaint_firstname - This is the users first name.
# complaint_lastname - This is the users last name.
# complaint_emailaddr - This is the users email address.
# complaint_type_id - This is the foregn key for mcComplaintTypes
# complaint_ad_id - This is the ad in question. Not always the ad being reported, could be ad who received spam.
# complaint_from_band_id - This is the band_id of the person who submitted this complaint
# complaint_about_band_id - This is the band_id of the person who has this complaint submitted against them.
# complaint_description - This is the additional information, submitted by the user.
# complaint_seen - This is a flag to tell mcClassifiedAds if the master admin has viewed this complaint.
# complaint_create_date - This is the date this complaint was submitted and posted to the database.
# complaint_update_date - This is the date the master admin has reviewed the complaint.
# complaint_admin_notes - This is the master admin notes.
# complaint_status - This is the status of this complaint.
# 0 - Pending Review
# 1 - Reviewed by master admin
# ########################################################################################################################
// Create mcComplaints Table
$tbl = "CREATE TABLE {$jamroom_db['mcComplaints']} (
complaint_id INT(7) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
complaint_firstname VARCHAR(30) NOT NULL DEFAULT '',
complaint_lastname VARCHAR(30) NOT NULL DEFAULT '',
complaint_emailaddr VARCHAR(75) NOT NULL DEFAULT '',
complaint_type_id INT(7) NOT NULL,
complaint_ad_id INT(7) NOT NULL,
complaint_from_band_id INT(7) NOT NULL,
complaint_about_band_id INT(7) NOT NULL,
complaint_description VARCHAR(255) NOT NULL DEFAULT '',
complaint_seen TINYINT(1) NOT NULL DEFAULT '1',
complaint_create_date VARCHAR(30) NOT NULL DEFAULT '',
complaint_update_date VARCHAR(30) NOT NULL DEFAULT '',
complaint_admin_notes varchar(255) NOT NULL DEFAULT '',
complaint_status TINYINT(1) NOT NULL DEFAULT '0'
)";
// Verify mcComplaints Table
dbVerifyTable($jamroom_db['mcComplaints'],$tbl);
# ########################################################################################################################
# ### mcComplaintTypes table
# ### Purpose: This table will hold all complaint types.
# ########################################################################################################################
# complaint_id - The unique ID for all complaints.
# complaint_name - The name or title of this complaint type. This is what is displayed on report abuse form
# complaint_description - The additional information, about this complaint type
# complaint_status - The status of this complaint type.
# 0 - Hidden (Will not be displayed to users in select box)
# 1 - Visible (Will be displayed in select box)
# ########################################################################################################################
// Create mcComplaintTypes Table
$tbl = "CREATE TABLE {$jamroom_db['mcComplaintTypes']} (
complaint_id INT(7) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
complaint_name VARCHAR(75) NOT NULL DEFAULT '',
complaint_description VARCHAR(255) NOT NULL DEFAULT '',
complaint_status TINYINT(1) NOT NULL DEFAULT '1'
)";
// Verify mcComplaintTypes Table
dbVerifyTable($jamroom_db['mcComplaintTypes'],$tbl);
# ### Lets check the mcComplaintTypes table for content. If exists and no content, lets populate with data.
// Lets build a query that will check for content in our mcComplaintTypes table.
$req = "SELECT COUNT(complaint_id) AS complaintcount
FROM {$jamroom_db['mcComplaintTypes']}";
$_rt = dbQuery($req,'SINGLE');
if (!checkType($_rt['complaintcount'],'number_nz')) {
// Build Query to populate mcComplaintTypes Table with sample data
$typereq = "INSERT INTO {$jamroom_db['mcComplaintTypes']} (
complaint_id, complaint_name, complaint_description, complaint_status
) VALUES
(1,'Spam/Fraud/Scam', 'Content of the ad or message is considered Spam/Fraud or a scam.','1'),
(2,'Inappropriate Contact', 'Contact from an ad you have posted that contained inappropriate subject.','1'),
(4,'Inappropriate Content', 'Content of the ad is inappropriate or does not belong in this category.','1'),
(4,'Other', 'Complaints that dont match any of the above.','1')
";
// Run $catreq Query
dbQuery($typereq);
}
# ########################################################################################################################
# ### mcComplaintStatus table
# ### Purpose: This table will hold the different status types available for each complaint
# ########################################################################################################################
# status_id - This is the unique id for this blacklist
# status_name - This is the band_id that is blacklisted
# status_description - This is the email that is blacklisted
# status_status - This is the status of this status: disabled(0)/enabled(1)
# ########################################################################################################################
// Create mcComplaintStatus Table
$tbl = "CREATE TABLE {$jamroom_db['mcComplaintStatus']} (
status_id INT(7) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
status_name varchar(30) UNSIGNED NOT NULL,
status_description text NOT NULL DEFAULT '',
status_status tinyint(1) NOT NULL DEFAULT '0')";
// Verify mcComplaintStatus Table
dbVerifyTable($jamroom_db['mcComplaintStatus'],$tbl);
# ### Lets check the mcComplaintStatus table for content. If exists and no content, lets populate with data.
// Lets build a query that will check for content in our mcComplaintStatus table.
$req = "SELECT COUNT(status_id) AS statuscount
FROM {$jamroom_db['mcComplaintStatus']}";
$_rt = dbQuery($req,'SINGLE');
if (!checkType($_rt['statuscount'],'number_nz')) {
// Build Query to populate mcBlackListTypes Table with sample data
$statReq = "INSERT INTO {$jamroom_db['mcComplaintStatus']} (
status_id, status_name, status_description, status_status
) VALUES
(1,'Pending', 'This is a new complaint. No action has been taken at this time.','1'),
(2,'Ignore', 'Currently no action has been taken. This complaint will remain in the complaints list, in case similar complaints to this one, come through.','1'),
(3,'Warning Issued', 'Warning has been issued. See admin notes for more details','1'),
(4,'Loss of feature','User has lost privilege of posting classified ads. See admin notes for more details'),
(3,'Suspended', 'The ad/user has been suspended from posting or responding to classified ads. See admin notes for more details.','1'),
(3,'Deleted', 'The ad/user has been deleted. See admin notes for more details','1'),
(3,'User blacklisted', 'Banned from both posting as well as responding to classified ads.','1'),
(3,'Delete Complaint', 'This complaint is no longer needed. Delete on next purging!','1')";
// Run $statReq Query
dbQuery($statReq);
}
// Jamroom Settings
$tbl = "mcClassifiedAds_cycle VARCHAR(4) NOT NULL DEFAULT '30',
mcClassifiedAds_newCount INT(4) NOT NULL DEFAULT '5',
mcClassifiedAds_renewalThreshold INT(4) NOT NULL DEFAULT '7',
mcClassifiedAds_uselogger CHAR(3) NOT NULL DEFAULT 'yes',
mcClassifiedAds_useRegional CHAR(3) NOT NULL DEFAULT 'yes',
mcClassifiedAds_useVisibility CHAR(3) NOT NULL DEFAULT 'yes',
mcClassifiedAds_useUploads CHAR(3) NOT NULL DEFAULT 'yes',
mcClassifiedAds_aImgWidth INT(4) NOT NULL DEFAULT '200',
mcClassifiedAds_cImgwidth INT(4) NOT NULL DEFAULT '150',
mcClassifiedAds_useAbuseButton CHAR(3) NOT NULL DEFAULT 'yes',
mcClassifiedAds_useBBCode CHAR(3) NOT NULL DEFAULT 'yes',
mcClassifiedAds_catstatus TINYINT(1) NOT NULL DEFAULT '1',
mcClassifiedAds_defaultcat INT(7) NOT NULL DEFAULT '1',
mcClassifiedAds_pagebreak INT(7) NOT NULL DEFAULT '25',
mcClassifiedAds_adbrowserpagebreak INT(7) NOT NULL DEFAULT '25'";
dbVerifyTable($jamroom_db['settings'],$tbl);
// Jamroom Quota
$tbl = "quota_mcClassifiedAds_access CHAR(3) NOT NULL DEFAULT 'no',
quota_mcClassifiedAds_useStartDate CHAR(3) NOT NULL DEFAULT 'no',
quota_mcClassifiedAds_useRegional CHAR(3) NOT NULL DEFAULT 'no',
quota_mcClassifiedAds_allowRenewals CHAR(3) NOT NULL DEFAULT 'no',
quota_mcClassifiedAds_allowUploads CHAR(3) NOT NULL DEFAULT 'no',
quota_mcClassifiedAds_allowBBCode CHAR(3) NOT NULL DEFAULT 'no',
quota_mcClassifiedAds_maxAdPost INT(7) NOT NULL DEFAULT '5',
quota_mcClassifiedAds_approvalDefault TINYINT(1) NOT NULL DEFAULT '1',
quota_mcClassifiedAds_allowPremiumPosts CHAR(3) NOT NULL DEFAULT 'no'";
dbVerifyTable($jamroom_db['quota'],$tbl);
?>
