oscaffiliate 2.02 installing problem

[daggy]

Hi!

 

I'm having a couple of problems trying to install the osc affiliate 2.02.

 

I didn't understand how to set up the databases.

 

Do i have to do it manually in phpmyadmin ?

 

 

-Dag

http://bluebox.no/~nshop/

[dkap]

Yes, that would be the way to do it.

 

Dan

[daggy]

I am quite new to databases. I have installed a database called nshop - but those in affiliate.sql - what do I do with those ?

[daggy]

I mean these:

 

How do i install these ?

 

# $Id: affiliate.sql,v 1.20 2003/07/12 13:13:38 simarilius Exp $

#

# OSC-Affiliate

#

# Contribution based on:

#

# osCommerce, Open Source E-Commerce Solutions

# http://www.oscommerce.com

#

# Copyright © 2002 - 2003 osCommerce

#

# Released under the GNU General Public License

#

# NOTE: * Please make any modifications to this file by hand!

# * DO NOT use a mysqldump created file for new changes!

# * Please take note of the table structure, and use this

# structure as a standard for future modifications!

# * Any tables you add here should be added in admin/backup.php

# and in catalog/install/includes/functions/database.php

# * To see the 'diff'erence between MySQL databases, use

# the mysqldiff perl script located in the extras

# directory of the 'catalog' module.

# * Comments should be like these, full line comments.

# (don't use inline comments)

 

DROP TABLE IF EXISTS affiliate_affiliate;

CREATE TABLE affiliate_affiliate (

affiliate_id int(11) NOT NULL auto_increment,

affiliate_gender char(1) NOT NULL default '',

affiliate_firstname varchar(32) NOT NULL default '',

affiliate_lastname varchar(32) NOT NULL default '',

affiliate_dob datetime NOT NULL default '0000-00-00 00:00:00',

affiliate_email_address varchar(96) NOT NULL default '',

affiliate_telephone varchar(32) NOT NULL default '',

affiliate_fax varchar(32) NOT NULL default '',

affiliate_password varchar(40) NOT NULL default '',

affiliate_homepage varchar(96) NOT NULL default '',

affiliate_street_address varchar(64) NOT NULL default '',

affiliate_suburb varchar(64) NOT NULL default '',

affiliate_city varchar(32) NOT NULL default '',

affiliate_postcode varchar(10) NOT NULL default '',

affiliate_state varchar(32) NOT NULL default '',

affiliate_country_id int(11) NOT NULL default '0',

affiliate_zone_id int(11) NOT NULL default '0',

affiliate_agb tinyint(4) NOT NULL default '0',

affiliate_company varchar(60) NOT NULL default '',

affiliate_company_taxid varchar(64) NOT NULL default '',

affiliate_commission_percent DECIMAL(4,2) NOT NULL default '0.00',

affiliate_payment_check varchar(100) NOT NULL default '',

affiliate_payment_paypal varchar(64) NOT NULL default '',

affiliate_payment_bank_name varchar(64) NOT NULL default '',

affiliate_payment_bank_branch_number varchar(64) NOT NULL default '',

affiliate_payment_bank_swift_code varchar(64) NOT NULL default '',

affiliate_payment_bank_account_name varchar(64) NOT NULL default '',

affiliate_payment_bank_account_number varchar(64) NOT NULL default '',

affiliate_date_of_last_logon datetime NOT NULL default '0000-00-00 00:00:00',

affiliate_number_of_logons int(11) NOT NULL default '0',

affiliate_date_account_created datetime NOT NULL default '0000-00-00 00:00:00',

affiliate_date_account_last_modified datetime NOT NULL default '0000-00-00 00:00:00',

affiliate_lft int(11) NOT NULL,

affiliate_rgt int(11) NOT NULL,

affiliate_root int(11) NOT NULL,

affiliate_newsletter char(1) NOT NULL default '1',

PRIMARY KEY (affiliate_id)

);

 

DROP TABLE IF EXISTS affiliate_banners;

CREATE TABLE affiliate_banners (

affiliate_banners_id int(11) NOT NULL auto_increment,

affiliate_banners_title varchar(64) NOT NULL default '',

affiliate_products_id int(11) NOT NULL default '0',

affiliate_banners_image varchar(64) NOT NULL default '',

affiliate_banners_group varchar(10) NOT NULL default '',

affiliate_banners_html_text text,

affiliate_expires_impressions int(7) default '0',

affiliate_expires_date datetime default NULL,

affiliate_date_scheduled datetime default NULL,

affiliate_date_added datetime NOT NULL default '0000-00-00 00:00:00',

affiliate_date_status_change datetime default NULL,

affiliate_status int(1) NOT NULL default '1',

PRIMARY KEY (affiliate_banners_id)

);

 

DROP TABLE IF EXISTS affiliate_banners_history;

CREATE TABLE affiliate_banners_history (

affiliate_banners_history_id int(11) NOT NULL auto_increment,

affiliate_banners_products_id int(11) NOT NULL default '0',

affiliate_banners_id int(11) NOT NULL default '0',

affiliate_banners_affiliate_id int(11) NOT NULL default '0',

affiliate_banners_shown int(11) NOT NULL default '0',

affiliate_banners_clicks tinyint(4) NOT NULL default '0',

affiliate_banners_history_date date NOT NULL default '0000-00-00',

PRIMARY KEY (affiliate_banners_history_id,affiliate_banners_products_id)

);

 

DROP TABLE IF EXISTS affiliate_clickthroughs;

CREATE TABLE affiliate_clickthroughs (

affiliate_clickthrough_id int(11) NOT NULL auto_increment,

affiliate_id int(11) NOT NULL default '0',

affiliate_clientdate datetime NOT NULL default '0000-00-00 00:00:00',

affiliate_clientbrowser varchar(200) default 'Could Not Find This Data',

affiliate_clientip varchar(50) default 'Could Not Find This Data',

affiliate_clientreferer varchar(200) default 'none detected (maybe a direct link)',

affiliate_products_id int(11) default '0',

affiliate_banner_id int(11) NOT NULL default '0',

PRIMARY KEY (affiliate_clickthrough_id),

KEY refid (affiliate_id)

);

 

DROP TABLE IF EXISTS affiliate_news;

CREATE TABLE `affiliate_news` (

`news_id` int(11) NOT NULL auto_increment,

`headline` varchar(255) NOT NULL default '',

`content` text NOT NULL,

`date_added` datetime NOT NULL default '0000-00-00 00:00:00',

`STATUS` tinyint(1) NOT NULL default '0',

PRIMARY KEY (`news_id`)

) TYPE=MyISAM AUTO_INCREMENT=3 ;

 

DROP TABLE IF EXISTS affiliate_newsletters;

CREATE TABLE `affiliate_newsletters` (

`affiliate_newsletters_id` int(11) NOT NULL auto_increment,

`title` varchar(255) NOT NULL default '',

`content` text NOT NULL,

`module` varchar(255) NOT NULL default '',

`date_added` datetime NOT NULL default '0000-00-00 00:00:00',

`date_sent` datetime default NULL,

`status` int(1) default NULL,

`locked` int(1) default '0',

PRIMARY KEY (`affiliate_newsletters_id`)

) TYPE=MyISAM AUTO_INCREMENT=1 ;

 

DROP TABLE IF EXISTS affiliate_payment;

CREATE TABLE affiliate_payment (

affiliate_payment_id int(11) NOT NULL auto_increment,

affiliate_id int(11) NOT NULL default '0',

affiliate_payment decimal(15,2) NOT NULL default '0.00',

affiliate_payment_tax decimal(15,2) NOT NULL default '0.00',

affiliate_payment_total decimal(15,2) NOT NULL default '0.00',

affiliate_payment_date datetime NOT NULL default '0000-00-00 00:00:00',

affiliate_payment_last_modified datetime NOT NULL default '0000-00-00 00:00:00',

affiliate_payment_status int(5) NOT NULL default '0',

affiliate_firstname varchar(32) NOT NULL default '',

affiliate_lastname varchar(32) NOT NULL default '',

affiliate_street_address varchar(64) NOT NULL default '',

affiliate_suburb varchar(64) NOT NULL default '',

affiliate_city varchar(32) NOT NULL default '',

affiliate_postcode varchar(10) NOT NULL default '',

affiliate_country varchar(32) NOT NULL default '0',

affiliate_company varchar(60) NOT NULL default '',

affiliate_state varchar(32) NOT NULL default '0',

affiliate_address_format_id int(5) NOT NULL default '0',

affiliate_last_modified datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (affiliate_payment_id)

);

 

DROP TABLE IF EXISTS affiliate_payment_status;

CREATE TABLE affiliate_payment_status (

affiliate_payment_status_id int(11) NOT NULL default '0',

affiliate_language_id int(11) NOT NULL default '1',

affiliate_payment_status_name varchar(32) NOT NULL default '',

PRIMARY KEY (affiliate_payment_status_id,affiliate_language_id),

KEY idx_affiliate_payment_status_name (affiliate_payment_status_name)

);

 

DROP TABLE IF EXISTS affiliate_payment_status_history;

CREATE TABLE affiliate_payment_status_history (

affiliate_status_history_id int(11) NOT NULL auto_increment,

affiliate_payment_id int(11) NOT NULL default '0',

affiliate_new_value int(5) NOT NULL default '0',

affiliate_old_value int(5) default NULL,

affiliate_date_added datetime NOT NULL default '0000-00-00 00:00:00',

affiliate_notified int(1) default '0',

PRIMARY KEY (affiliate_status_history_id)

);

 

DROP TABLE IF EXISTS affiliate_sales;

CREATE TABLE affiliate_sales (

affiliate_id int(11) NOT NULL default '0',

affiliate_date datetime NOT NULL default '0000-00-00 00:00:00',

affiliate_browser varchar(100) NOT NULL default '',

affiliate_ipaddress varchar(20) NOT NULL default '',

affiliate_orders_id int(11) NOT NULL default '0',

affiliate_value decimal(15,2) NOT NULL default '0.00',

affiliate_payment decimal(15,2) NOT NULL default '0.00',

affiliate_clickthroughs_id int(11) NOT NULL default '0',

affiliate_billing_status int(5) NOT NULL default '0',

affiliate_payment_date datetime NOT NULL default '0000-00-00 00:00:00',

affiliate_payment_id int(11) NOT NULL default '0',

affiliate_percent DECIMAL(4,2) NOT NULL default '0.00',

affiliate_salesman int(11) NOT NULL default '0',

PRIMARY KEY (affiliate_orders_id,affiliate_id)

);

 

INSERT INTO affiliate_payment_status VALUES (0, 1, 'Pending');

INSERT INTO affiliate_payment_status VALUES (1, 1, 'Paid');

 

 

INSERT INTO configuration_group VALUES (900, 'Affiliate Program', 'Options for the Affiliate Program', 50, 1);

INSERT INTO configuration VALUES ('','E-Mail Address', 'AFFILIATE_EMAIL_ADDRESS', '<[email protected]>', 'The E Mail Address for the Affiliate Program', 900, 1, NULL, now(), NULL, NULL);

INSERT INTO configuration VALUES ('','Affiliate Pay Per Sale Payment % Rate', 'AFFILIATE_PERCENT', '10.0000', 'Percentage Rate for the Affiliate Program', 900, 2, NULL, now(), NULL, NULL);

INSERT INTO configuration VALUES ('','Payment Threshold', 'AFFILIATE_THRESHOLD', '50.00', 'Payment Threshold for paying affiliates', 900, 3, NULL, now(), NULL, NULL);

INSERT INTO configuration VALUES ('','Cookie Lifetime', 'AFFILIATE_COOKIE_LIFETIME', '7200', 'How long does the click count (seconds) if customer comes back', 900, 4, NULL, now(), NULL, NULL);

INSERT INTO configuration VALUES ('','Billing Time', 'AFFILIATE_BILLING_TIME', '30', 'Orders billed must be at least "30" days old.<br>This is needed if a order is refunded', 900, 5, NULL, now(), NULL, NULL);

INSERT INTO configuration VALUES ('','Order Min Status', 'AFFILIATE_PAYMENT_ORDER_MIN_STATUS', '3', 'The status an order must have at least, to be billed', 900, 6, NULL, now(), NULL, NULL);

INSERT INTO configuration VALUES ('','Pay Affiliates with check', 'AFFILIATE_USE_CHECK', 'true', 'Pay Affiliates with check', 900, 7, NULL, now(), NULL,'tep_cfg_select_option(array(\'true\', \'false\'), ');

INSERT INTO configuration VALUES ('','Pay Affiliates with PayPal', 'AFFILIATE_USE_PAYPAL', 'true', 'Pay Affiliates with PayPal', 900, 8, NULL, now(), NULL,'tep_cfg_select_option(array(\'true\', \'false\'), ');

INSERT INTO configuration VALUES ('','Pay Affiliates by Bank', 'AFFILIATE_USE_BANK', 'true', 'Pay Affiliates by Bank', 900, 9, NULL, now(), NULL,'tep_cfg_select_option(array(\'true\', \'false\'), ');

INSERT INTO configuration VALUES ('','Individual Affiliate Percentage', 'AFFILATE_INDIVIDUAL_PERCENTAGE', 'true', 'Allow per Affiliate provision', 900, 10, NULL, now(), NULL,'tep_cfg_select_option(array(\'true\', \'false\'), ');

INSERT INTO configuration VALUES ('','Use Affiliate-tier' , 'AFFILATE_USE_TIER', 'false', 'Multilevel Affiliate provisions', 900, 11, NULL, now(), NULL,'tep_cfg_select_option(array(\'true\', \'false\'), ');

INSERT INTO configuration VALUES ('','Number of Tierlevels', 'AFFILIATE_TIER_LEVELS', '0', 'Number of Tierlevels', 900, 12, NULL, now(), NULL, NULL);

INSERT INTO configuration VALUES ('','Percentage Rate for the Tierlevels', 'AFFILIATE_TIER_PERCENTAGE', '8.00;5.00;1.00', 'Percent Rates for the tierlevels<br>Example: 8.00;5.00;1.00', 900, 13, NULL, now(), NULL, NULL);

INSERT INTO configuration VALUES ('','Affiliate News', 'MAX_DISPLAY_AFFILIATE_NEWS', '3', 'Maximum number of items to display on the Affiliate News page', 900, 14, NULL, now(), NULL, NULL);

[daggy]

Still haven't figured it out... Please help me...

[Criz]

Dear Daggy,

 

in your sql admin, you must look for a field called query of process. I use blacksql and have this line: Run SQL query/queries on database.

 

Here you get a window, you paste the lines (the lines are actually sql commands) in there and press Go.

 

Then it automatically generates the new tables etc. But be carefull, things can go pretty wrong when you mess with you database. Make sure you have a recent backup.

 

Hope this helps you out.

 

Regards,

 

Chris