###############################################################################
### Dues Calculator. Ticket 3364
###############################################################################
INSERT INTO `prj_mncar`.`mn_zz_Settings` (`id` ,`cat` ,`name` ,`label` ,`value` ,`type` ,`options` ,`added` ,`changed` )
VALUES (NULL , 'dues-calculator', 'dues_calculator_options', '', '', 'text', '', NOW( ) , NOW( ) );

ALTER TABLE `mn_DuesCalculator` DROP `amount`;

###############################################################################
### Member changes. Ticket 3907
###############################################################################
DELETE FROM `mn_DMember` WHERE `mn_DMember`.`id` = 5 LIMIT 1;
DELETE FROM `mn_DMember` WHERE `mn_DMember`.`id` = 6 LIMIT 1;

DELETE FROM `mn_DMemberType` WHERE `mn_DMemberType`.`id` = 6 LIMIT 1;
DELETE FROM `mn_DMemberType` WHERE `mn_DMemberType`.`id` = 8 LIMIT 1;
DELETE FROM `mn_DMemberType` WHERE `mn_DMemberType`.`id` = 9 LIMIT 1;

ALTER TABLE `mn_Member` DROP `id_current_mcpe_status`;
###############################################################################
### Member edit and search changes - 2008-10-23. Ticket 3943
###############################################################################
ALTER TABLE `mn_Member` DROP `inbox_type`

###############################################################################
### Broadcast Email Market Values (STS # 732). Ticket 3959
###############################################################################
DROP TABLE IF EXISTS `mn_DArea`;
CREATE TABLE IF NOT EXISTS `mn_DArea` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `info` enum('n','y') NOT NULL default 'y',
  `sorter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;

--
-- ���� ����� ������ `mn_DArea`
--

INSERT INTO `mn_DArea` (`id`, `title`, `info`, `sorter`) VALUES
(1, 'Minneapolis CBD', 'y', 0),
(2, 'Southeast', 'y', 0),
(3, 'Southwest', 'y', 0),
(4, 'St. Paul CBD', 'y', 0),
(5, 'St. Paul Suburban', 'y', 0),
(6, 'West & Northwest', 'y', 0),
(7, 'St Cloud', 'y', 0),
(8, 'Rochester', 'y', 0),
(9, 'East & NorthEast', 'y', 0),
(10, 'Northeast', 'n', 0),
(11, 'Nortwest', 'n', 0),
(12, 'East', 'n', 0),
(13, 'CBD', 'n', 0),
(14, 'Other', 'n', 0);

DROP TABLE IF EXISTS `mn_DAreaType`;
CREATE TABLE IF NOT EXISTS `mn_DAreaType` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `sorter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- ���� ����� ������ `mn_DAreaType`
--

INSERT INTO `mn_DAreaType` (`id`, `title`, `sorter`) VALUES
(1, 'Twin Cities Industrial Markets', 1),
(2, 'Twin Cities Office Markets', 2),
(3, 'Twin Cities Retail Markets', 3),
(4, 'Out of Metro Markets', 4);

DROP TABLE IF EXISTS `mn_DAreaType2Area`;
CREATE TABLE IF NOT EXISTS `mn_DAreaType2Area` (
  `id_area_type` int(11) NOT NULL default '0',
  `id_area` int(11) NOT NULL default '0',
  `sorter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id_area_type`,`id_area`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- ���� ����� ������ `mn_DAreaType2Area`
--

INSERT INTO `mn_DAreaType2Area` (`id_area_type`, `id_area`, `sorter`) VALUES
(1, 10, 1),
(1, 3, 2),
(1, 2, 3),
(1, 11, 4),
(1, 12, 5),
(2, 1, 1),
(2, 6, 2),
(2, 3, 3),
(2, 2, 4),
(2, 4, 5),
(2, 5, 6),
(3, 10, 1),
(3, 3, 2),
(3, 2, 3),
(3, 11, 4),
(3, 13, 5),
(4, 8, 1),
(4, 7, 2),
(4, 14, 3);

ALTER TABLE `mn_Member2EmailArea` ADD `id_area_type` INT NOT NULL AFTER `id_parent` ;
ALTER TABLE `mn_Member2EmailArea` DROP PRIMARY KEY;
ALTER TABLE `mn_Member2EmailArea` DROP PRIMARY KEY, ADD PRIMARY KEY ( `id_parent` , `id_area_type` , `id_area` );

ALTER TABLE `mn_DSpecialty` ADD `is_subcategory` ENUM( 'n', 'y' ) NOT NULL AFTER `title` ;
UPDATE `mn_DSpecialty` SET `is_subcategory` = 'y' WHERE `mn_DSpecialty`.`id` =1 LIMIT 1 ;
UPDATE `mn_DSpecialty` SET `is_subcategory` = 'y' WHERE `mn_DSpecialty`.`id` =2 LIMIT 1 ;
UPDATE `mn_DSpecialty` SET `is_subcategory` = 'y' WHERE `mn_DSpecialty`.`id` =4 LIMIT 1 ;


ALTER TABLE `mn_Email2Area` ADD `id_area_type` INT NOT NULL AFTER `id_parent` ;
ALTER TABLE `mn_Email2Area` DROP PRIMARY KEY, ADD PRIMARY KEY ( `id_parent` , `id_area_type` , `id_area` );

ALTER TABLE `mn_Email` ADD `market_group` ENUM( 'twin_cities', 'out_of_metro' ) NOT NULL AFTER `subject_radio3` ,
ADD `market_subgroup` INT NOT NULL AFTER `market_group` ;


###############################################################################
### Event Doc management -  Ticket 3823 - Event Modifications (STS #679)
###############################################################################

ALTER TABLE `mn_EventDocumentofCommerce` DROP PRIMARY KEY ,
ADD UNIQUE `doc_number` ( `doc_number` );
ALTER TABLE `mn_EventDocumentofCommerce` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;

###############################################################################
### Bio/Profile Categories. Ticket 4087
###############################################################################
ALTER TABLE `mn_DArea` CHANGE `info` `profile` ENUM( 'n', 'y' ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'y';
ALTER TABLE `mn_DArea` ADD `info` ENUM( 'n', 'y' ) NOT NULL AFTER `title` ;
update `mn_DArea` set info = profile WHERE 1;

ALTER TABLE `mn_DSpecialty` CHANGE `hidden` `profile` ENUM( 'n', 'y' ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'y';
UPDATE `mn_DSpecialty` SET profile = 'y' WHERE 1;
ALTER TABLE `mn_DSpecialty` ADD `info` ENUM( 'n', 'y' ) NOT NULL AFTER `profile` ;
UPDATE `mn_DSpecialty` SET info = 'y' WHERE 1;

CREATE TABLE IF NOT EXISTS `mn_DLineOfWork` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `sorter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

INSERT INTO `mn_DLineOfWork` (`id`, `title`, `sorter`) VALUES
(1, 'Transactions', 0),
(2, 'Consulting', 0),
(3, 'Appraisal', 0),
(4, 'Development', 0),
(5, 'Design', 0);

CREATE TABLE IF NOT EXISTS `mn_Member2LineOfWork` (
  `id_parent` int(11) NOT NULL default '0',
  `id_line_of_work` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id_parent`,`id_line_of_work`),
  KEY `id_parent` (`id_parent`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `mn_Member` ADD `other_line_of_work` VARCHAR( 255 ) NOT NULL AFTER `bio` ;
ALTER TABLE `mn_Member` DROP `salutation`;

###############################################################################
### Bio/Profile Categories. Ticket 4087. Changes for Import
###############################################################################

INSERT INTO `mn_DSpecialty` (`id`, `title`, `is_subcategory`, `profile`, `info`, `sorter`) VALUES
(6 , 'Appraisal', 'n', 'n', 'y', '7'),
(7 , 'Multi-Family', 'n', 'n', 'y', '8'),
(8 , 'Developer', 'n', 'n', 'y', '9'),
(9 , 'Hospitality', 'n', 'n', 'y', '10'),
(10 , 'Tenant Rep', 'n', 'n', 'y', '11');

ALTER TABLE `mn_Member` DROP `speciality_other`, DROP `area_other` ;
ALTER TABLE `mn_Member` ADD `other_area` VARCHAR( 255 ) NOT NULL AFTER `bio` ,
ADD `other_specialty` VARCHAR( 255 ) NOT NULL AFTER `other_area` ;

UPDATE `mn_DArea` SET `info` = 'n' WHERE `mn_DArea`.`id` =6 LIMIT 1 ;
UPDATE `mn_DArea` SET `info` = 'n' WHERE `mn_DArea`.`id` =9 LIMIT 1 ;
UPDATE `mn_DArea` SET `info` = 'y' WHERE `mn_DArea`.`id` =10 LIMIT 1 ;
UPDATE `mn_DArea` SET `info` = 'y' WHERE `mn_DArea`.`id` =11 LIMIT 1 ;
UPDATE `mn_DArea` SET `info` = 'n' WHERE `mn_DArea`.`id` =5 LIMIT 1 ;

INSERT INTO `mn_DArea` (`id`, `title`, `profile`, `info`, `sorter`) VALUES
(NULL, 'West', 'n', 'n', '15'),
(NULL, 'Out of Metro', 'n', 'y', '16');

###############################################################################
### Event. New field for description into .vcs file - Ticket 3997 - List of top priotiry things from Meeting on Oct. 27 (STS # 756)
###############################################################################
ALTER TABLE `mn_Event` ADD `description_vcs` TEXT NOT NULL AFTER `description` ;

###############################################################################
### Ticket 3996  -   Estimate improving how the adds and sponsors function to allow different markets to see different information (STS # 753)
###############################################################################

CREATE TABLE IF NOT EXISTS `mn_DSponsorAreaType` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `sorter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `mn_DSponsorAreaType` (`id`, `title`, `sorter`) VALUES
(1, 'Twin Cities', 1),
(2, 'Saint Cloud', 1),
(3, 'Rochester', 3);

CREATE TABLE IF NOT EXISTS `mn_Sponsor2AreaType` (
  `id_parent` int(11) NOT NULL default '0',
  `id_sponsor_area_type` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id_parent`,`id_sponsor_area_type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

###############################################################################
### Ticket 4245	  -   (Change) Functionality to have a support login (STS # 821)
###############################################################################
INSERT INTO `mn_DMember` (`id` ,`title` ,`sorter` ) VALUES ('9', 'Support Account', '9');
ALTER TABLE `mn_Member` ADD `id_autologin_member` INT NOT NULL AFTER `email_signature` ;
INSERT INTO `mn_zz_DocItemType` (`name` ,`title` ) VALUES ('Member/SupportRequest', 'Member Support Account Request');

CREATE TABLE IF NOT EXISTS `mn_SupportAccountRequest` (
  `id` int(11) NOT NULL auto_increment,
  `id_support_account` int(11) default NULL,
  `id_requested_member` int(11) default NULL,
  `first_name` varchar(255) NOT NULL default '',
  `middle_initial` char(1) NOT NULL default '',
  `last_name` varchar(255) NOT NULL default '',
  `primary_phone_title` varchar(255) NOT NULL default '',
  `primary_phone` varchar(255) NOT NULL default '',
  `secondary_phone_title` varchar(255) NOT NULL default '',
  `secondary_phone` varchar(255) NOT NULL default '',
  `fax` varchar(255) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  `request_email` varchar(255) NOT NULL default '',
  `authorized` enum('y','n') NOT NULL default 'n',
  `access_code` varchar(32) default NULL,
  `added` datetime NOT NULL default '0000-00-00 00:00:00',
  `changed` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `access_code` (`access_code`)
) ENGINE=MyISAM ;


CREATE TABLE IF NOT EXISTS `mn_MemberSupportRequest` (
  `id` int(11) NOT NULL auto_increment,
  `id_member` int(11) NOT NULL default '0',
  `id_support_account` int(11) default '0',
  `support_email` varchar(255) NOT NULL default '',
  `access_code` varchar(32) default NULL,
  `authorized` enum('n','y') NOT NULL default 'n',
  `added` datetime NOT NULL default '0000-00-00 00:00:00',
  `changed` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `access_code` (`access_code`)
) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `mn_Member2Member` (
  `id_parent` int(11) NOT NULL default '0',
  `id_member` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id_parent`,`id_member`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


###############################################################################
### Ticket 4247  -   Loose Ends that need to be tied up (STS # 857)
###############################################################################
UPDATE `mn_EventType` SET `title` = 'Brokerage Basics' WHERE `mn_EventType`.`id` =3 LIMIT 1 ;

###############################################################################
### Ticket 4318  -   Things from Phone Meeting on Nov 25, 2008
###############################################################################
ALTER TABLE `mn_Member` ADD `is_accepted_forum_legal_agreement` ENUM( 'n', 'y' ) NOT NULL DEFAULT 'n' AFTER `is_accepted_broadcast_legal_agreement` ;

###############################################################################
### Task 4596  -   (Change) Broadcast Size Estimate
###############################################################################
ALTER TABLE `mn_Email` ADD `property_size_min` FLOAT NULL AFTER `property_size` ,
ADD `property_size_max` FLOAT NULL AFTER `property_size_min` ;
ALTER TABLE `mn_Email` ADD `use_range` ENUM( 'n', 'y' ) NOT NULL DEFAULT 'n' AFTER `property_unit` ;
UPDATE `mn_Email` SET `use_range` = 'y', `property_size_min` = `property_size`, `property_size_max` = `property_size`;
ALTER TABLE `mn_Email` DROP `property_size`;

###############################################################################
### Task 5090  -   (Change) Broadcast Email Quick Send
###############################################################################
ALTER TABLE `mn_Email2Individual` CHANGE `error` `error` ENUM( 'n', 'y' ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL;
ALTER TABLE `mn_Email2Individual` ADD INDEX ( `error` );
ALTER TABLE `mn_Email2Individual` ADD `sorter` INT NULL AFTER `id_individual` ;
ALTER TABLE `mn_Email2Individual` ADD INDEX ( `sorter` );

###############################################################################
### Task 5772  -   (Change) Broadcast Email Changes - 29-07-2009
###############################################################################
### DB Structure changes  
###############################################################################
CREATE TABLE IF NOT EXISTS `mn_DEmailGroupType` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `sorter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO `mn_DEmailGroupType` (`id`, `title`, `sorter`) VALUES
(1, 'Office', 1),
(2, 'Land', 2),
(3, 'Retail', 3),
(4, 'Investment', 4),
(5, 'Multi-Unit Housing', 5),
(6, 'Special Use', 6),
(7, 'Rochester', 7),
(8, 'St.Cloud', 8),
(9, 'Outstate', 9);

CREATE TABLE IF NOT EXISTS `mn_DEmailSubType` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `sorter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO `mn_DEmailSubType` (`id`, `title`, `sorter`) VALUES
(1, 'Lease', 1),
(2, 'SubLease', 2),
(3, 'Sale', 3),
(4, 'Sale or Lease', 4);

CREATE TABLE IF NOT EXISTS `mn_DPrimaryUse` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `sorter` int(11) NOT NULL default '0',
  `type` enum('listing','requirement','listing requirement') NOT NULL default 'listing',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO `mn_DPrimaryUse` (`id`, `title`, `sorter`, `type`) VALUES
(1, 'Office', 1, 'listing requirement'),
(2, 'Industrial', 2, 'listing'),
(3, 'Land', 3, 'listing requirement'),
(4, 'Retail', 4, 'listing requirement'),
(5, 'Investment', 5, 'requirement'),
(6, 'Office/Industrial', 6, 'listing'),
(7, 'Office/Retail', 7, 'listing'),
(8, 'Special Use', 8, 'listing requirement'),
(9, 'Rochester', 9, 'requirement'),
(10, 'St. Cloud', 10, 'requirement'),
(11, 'Outstate', 11, 'requirement'),
(12, 'Multi-Unit Housing', 12, 'listing');

CREATE TABLE IF NOT EXISTS `mn_DPropertyLocation` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `sorter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO `mn_DPropertyLocation` (`id`, `title`, `sorter`) VALUES
(1, 'NE Market', 1),
(2, 'NW Market', 2),
(3, 'SE Market', 3),
(4, 'SW Market', 4),
(5, 'Minneapolis', 5),
(6, 'St. Paul', 6),
(7, 'Rochester Area', 7),
(8, 'St. Cloud Area', 8),
(9, 'Outstate - NE', 9),
(10, 'Outstate - NW', 10),
(11, 'Outstate - SE', 11),
(12, 'Outstate - SW', 12);

CREATE TABLE IF NOT EXISTS `mn_Email2GroupType` (
  `email_id` int(11) NOT NULL default '0',
  `group_id` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `mn_Email2SubType` (
  `email_id` int(11) NOT NULL default '0',
  `subtype_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`email_id`,`subtype_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `mn_Member2EmailGroup` (
  `member_id` int(11) NOT NULL default '0',
  `group_id` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `mn_Email` ADD `location_id` INT NOT NULL AFTER `market_subgroup` ,
ADD `primary_use_id` INT NOT NULL AFTER `location_id` ,
ADD `investment` ENUM( 'n', 'y' ) NOT NULL DEFAULT 'n' AFTER `primary_use_id` ;

###############################################################################
### Email DB Conversion  
###############################################################################
UPDATE `mn_Email` SET `primary_use_id` = `id_specialty`;
UPDATE `mn_Email` SET `primary_use_id` = `market_subgroup` WHERE `id_specialty` = 5;
UPDATE `mn_Email` SET `primary_use_id` = 3 - `primary_use_id` WHERE `primary_use_id` < 3;
UPDATE `mn_Email` SET `primary_use_id` = 1 WHERE `primary_use_id` IN(5, 9, 10, 11);

UPDATE `mn_Email` SET `location_id` = 5 WHERE `id` IN (SELECT id_parent FROM mn_Email2Area WHERE id_area = 1);
UPDATE `mn_Email` SET `location_id` = 3 WHERE `id` IN (SELECT id_parent FROM mn_Email2Area WHERE id_area = 2);
UPDATE `mn_Email` SET `location_id` = 4 WHERE `id` IN (SELECT id_parent FROM mn_Email2Area WHERE id_area = 3);
UPDATE `mn_Email` SET `location_id` = 6 WHERE `id` IN (SELECT id_parent FROM mn_Email2Area WHERE id_area = 4);
UPDATE `mn_Email` SET `location_id` = 1 WHERE `id` IN (SELECT id_parent FROM mn_Email2Area WHERE id_area = 5);
UPDATE `mn_Email` SET `location_id` = 2 WHERE `id` IN (SELECT id_parent FROM mn_Email2Area WHERE id_area = 6);
UPDATE `mn_Email` SET `location_id` = 8 WHERE `id` IN (SELECT id_parent FROM mn_Email2Area WHERE id_area = 7);
UPDATE `mn_Email` SET `location_id` = 7 WHERE `id` IN (SELECT id_parent FROM mn_Email2Area WHERE id_area = 8);
UPDATE `mn_Email` SET `location_id` = 1 WHERE `id` IN (SELECT id_parent FROM mn_Email2Area WHERE id_area = 10);
UPDATE `mn_Email` SET `location_id` = 2 WHERE `id` IN (SELECT id_parent FROM mn_Email2Area WHERE id_area = 11);
UPDATE `mn_Email` SET `location_id` = 3 WHERE `id` IN (SELECT id_parent FROM mn_Email2Area WHERE id_area = 12);
UPDATE `mn_Email` SET `location_id` = 5 WHERE `id` IN (SELECT id_parent FROM mn_Email2Area WHERE id_area = 13);
UPDATE `mn_Email` SET `location_id` = 5 WHERE `id_type`<>1;

TRUNCATE TABLE `mn_Member2EmailGroup`;
INSERT INTO `mn_Member2EmailGroup` SELECT id_parent, id_specialty-1 FROM mn_Member2EmailSpecialty WHERE id_specialty>1;
INSERT INTO `mn_Member2EmailGroup` SELECT id_parent, 10 FROM mn_Member2EmailSpecialty WHERE id_specialty=1;
INSERT INTO `mn_Member2EmailGroup` SELECT id_parent, 7 FROM mn_Member2EmailArea WHERE id_area_type = 4 AND id_area = 8;
INSERT INTO `mn_Member2EmailGroup` SELECT id_parent, 8 FROM mn_Member2EmailArea WHERE id_area_type = 4 AND id_area = 7;
INSERT INTO `mn_Member2EmailGroup` SELECT id_parent, 9 FROM mn_Member2EmailArea WHERE id_area_type = 4 AND id_area = 14;
INSERT INTO `mn_Member2EmailGroup` SELECT id, 6 FROM mn_Member;

TRUNCATE TABLE mn_Email2SubType;
INSERT INTO mn_Email2SubType SELECT id, 1 FROM mn_Email WHERE subject_radio3 = 'lease';
INSERT INTO mn_Email2SubType SELECT id, 2 FROM mn_Email WHERE subject_radio3 = 'sublease';
INSERT INTO mn_Email2SubType SELECT id, 3 FROM mn_Email WHERE subject_radio3 = 'sale';
INSERT INTO mn_Email2SubType SELECT id, 4 FROM mn_Email WHERE subject_radio3 = 'sale_or_lease';


###############################################################################
### New pages - popup  
###############################################################################
INSERT INTO `mn_zz_Doc` (`id`, `alias`, `redirect_url`, `id_parent`, `handler`, `final`, `system`, `readonly`, `member_page`, `sorter`, `changed`, `added`) VALUES
(195, 'broadcast-email-type-tip', '', 43, 'PopupDoc', 'n', 'y', 'y', 'n', 3, '2009-07-30 12:09:21', '2009-07-28 09:22:49'),
(196, 'broadcast-email-group-tip', '', 43, 'PopupDoc', 'n', 'y', 'y', 'n', 36, '2009-07-28 16:47:11', '2009-07-28 09:31:04'),
(197, 'broadcast-email-location-tip', '', 43, 'PopupDoc', 'n', 'y', 'y', 'n', 38, '2009-07-28 16:47:21', '2009-07-28 09:38:01');

INSERT INTO `mn_zz_DocItem` (`id_doc`, `type`, `name`, `sorder`, `added`) VALUES
(195, 'HTMLBlock/Show', 'broadcast-email-type-tip', 0, '2009-07-28 16:45:55'),
(196, 'HTMLBlock/Show', 'broadcast-email-group-tip', 0, '2009-07-28 16:47:11'),
(197, 'HTMLBlock/Show', 'broadcast-email-location-tip', 0, '2009-07-28 16:47:21');

INSERT INTO `mn_zz_DocTitle` (`id_parent`, `lang`, `header`, `title`, `keyword`, `description`, `head`, `right_content`, `added`, `changed`) VALUES
(195, 'eng', 'How are broadcast email types defined?', 'How are broadcast email types defined?', '', '', '', '', '2009-07-28 09:22:49', '2009-07-30 12:09:21'),
(196, 'eng', '', 'Broadcast Email Group', '', '', '', '', '2009-07-28 09:31:04', '2009-07-28 16:47:11'),
(197, 'eng', '', 'Broadcast Email Market Boundaries', '', '', '', '', '2009-07-28 09:38:01', '2009-07-28 16:47:21');

INSERT INTO `mn_zz_HTMLBlock` (`id`, `name`, `added`, `changed`) VALUES
(183, 'broadcast-email-type-tip', '2009-07-28 09:22:49', '2009-07-30 12:09:21'),
(184, 'broadcast-email-group-tip', '2009-07-28 09:30:59', '2009-07-28 09:30:59'),
(185, 'broadcast-email-location-tip', '2009-07-28 09:37:52', '2009-07-28 09:37:52');

INSERT INTO `mn_zz_HTMLBlockContent` (`id_parent`, `lang`, `content`, `added`, `changed`) VALUES
(183, 'eng', '<p><strong>How are broadcast email types defined&#63;</strong></p>\r\n<p>Lorem ipsum exerci <a target="_blank" href="http://google.com">omnium</a> senserit at vis, eripuit inimicus imperdiet mei ne, ad est augue vocent contentiones:</p>\r\n<ul>\r\n    <li>Cu sea accusam voluptua,</li>\r\n    <li>cum et soleat legere euripidis.</li>\r\n    <li>Virtute imperdiet maiestatis no usu</li>\r\n    <li>at mea summo docendi.</li>\r\n</ul>\r\n<p>Ut clita imperdiet patrioque mei, ad pri explicari maiestatis conclusionemque. Sonet aeterno interpretaris pro at, usu novum corrumpit persequeris ut.  Prompta referrentur cum ad, te vix clita dolorum. In quem probo perpetua pri, te libris cetero volutpat eam. Per dolor semper pertinacia no, quando accusamus patrioque sit an, congue insolens evertitur cum an. Sit an vocibus volumus ancillae. Dicant salutatus interesset no vis. Ut viderer diceret aliquid qui, quod neglegentur ne sed.</p>', '2009-07-28 09:22:49', '2009-07-30 12:09:21'),
(184, 'eng', '<p>Broadcast Email Group</p>', '2009-07-28 09:30:59', '2009-07-28 09:30:59'),
(185, 'eng', '<p>Broadcast Email Market Boundaries</p>', '2009-07-28 09:37:52', '2009-07-28 09:37:52');



###############################################################################
### Task 6010  -   Category Changes
###############################################################################
CREATE TABLE IF NOT EXISTS `mn_DTypeOfBusiness` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `sorter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO `mn_DTypeOfBusiness` (`id`, `title`, `sorter`) VALUES
(1, 'Accounting', 0),
(2, 'Aerial Photography', 1),
(3, 'Architecture', 2),
(4, 'Attorneys', 3),
(5, 'Banking / Financing', 4),
(6, 'Contractor', 5),
(7, 'Development', 6),
(8, 'Engineering - Environmental', 7),
(9, 'Engineering - Structural', 8),
(10, 'Engineering - Mechanical', 9),
(11, 'Foundations', 10),
(12, 'Furniture', 11),
(13, 'Interior / Space Design', 12),
(14, 'Landscaping', 13),
(15, 'Media Publications', 14),
(16, 'Property Management', 15),
(17, 'Surveying', 16),
(18, 'Title', 17);

ALTER TABLE `mn_Member` ADD `other_type_of_business` VARCHAR( 255 ) NOT NULL AFTER `other_line_of_work` ;

###############################################################################
### Task 6207  -   (Change) Broadcast Email Changes (2) - 10-09-2009
###############################################################################
INSERT INTO `mn_DEmailGroupType` (`id`, `title`, `sorter`) VALUES
(10, 'Industrial', 10);

###############################################################################
### Task 6933  -   Make SEO improvements via instructions from MLT Group - 23-11-2009
###############################################################################
ALTER TABLE `mn_zz_DocTitle` ADD `menutitle` VARCHAR( 255 ) NOT NULL AFTER `title` ;

UPDATE `mn_zz_DocTitle` SET `menutitle` = `title`;

###############################################################################
### Task 7180  -   Build Exchange dues form and export - 21-12-2009
###############################################################################
ALTER TABLE `mn_Location` ADD `large_company` ENUM( 'n', 'y' ) NOT NULL AFTER `contact_phone`;
ALTER TABLE `mn_Location` ADD `market_id` INT NOT NULL DEFAULT 1 AFTER `large_company` ;
ALTER TABLE `mn_Location` ADD `dr_name` VARCHAR( 255 ) NOT NULL AFTER `id_designated_realtor` ,
ADD `dr_email` VARCHAR( 255 ) NOT NULL AFTER `dr_name` ,

ADD `dr_phone` VARCHAR( 255 ) NOT NULL AFTER `dr_email` ;
CREATE TABLE IF NOT EXISTS `mn_ExchangeDuesReportItems` (
  `id` int(11) NOT NULL auto_increment,
  `number` varchar(255) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `price` decimal(6,2) NOT NULL default '0.00',
  `mncar_market_id` tinyint(4) NOT NULL default '0',
  `member` enum('n','y') NOT NULL default 'n',
  `first_user` enum('n','y') NOT NULL default 'n',
  `sorter` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
INSERT INTO `mn_ExchangeDuesReportItems` (`id`, `number`, `description`, `price`, `mncar_market_id`, `member`, `first_user`, `sorter`) VALUES
(1, 'OFFICE', 'Quarterly Branch Fees', 90.00, 0, 'n', 'n', 1),
(2, 'OLM-QTR-PP', 'First User - MNCAR Member', 386.25, 1, 'y', 'y', 2),
(3, 'OLM-QTR-AP', 'Additional Users - MNCAR Member', 180.00, 1, 'y', 'n', 3),
(4, 'OLNM-QTR-PP', 'First User - Non-MNCAR Member', 402.50, 1, 'n', 'y', 4),
(5, 'OLNM-QTR-AP', 'Additional Users - Non-MNCAR Member', 188.00, 1, 'n', 'n', 5),
(6, 'OLM-QTR-PP-STCLOUD', 'First User - MNCAR Member, St Cloud Discount', 351.00, 2, 'y', 'y', 6),
(7, 'OLM-QTR-AP-STCLOUD', 'Additional User - MNCAR Member, St Cloud Discount', 62.50, 2, 'y', 'n', 7),
(8, 'OLNM-QTR-PP-STCLOUD', 'First User - Non-MNCAR Member, St Cloud Discount', 366.00, 2, 'n', 'y', 8),
(9, 'OLNM-QTR-AP-STCLOUD', 'Additional User - Non-MNCAR Member, St Cloud Discount', 62.50, 2, 'y', 'n', 9),
(10, 'OLM-QTR-PP-ROCHESTER', 'First User - MNCAR Member, Rochester Discount', 250.00, 3, 'y', 'y', 10),
(11, 'OLM-QTR-AP-ROCHESTER', 'Additional User - MNCAR Member, Rochester Discount', 57.50, 3, 'y', 'n', 11),
(12, 'OLNM-QTR-PP-ROCHESTER', 'First User - Non-MNCAR Member, Rochester Discount', 250.00, 3, 'n', 'y', 12),
(13, 'OLNM-QTR-AP-ROCHESTER', 'Additional User - Non-MNCAR Member, Rochester Discount', 57.50, 3, 'n', 'n', 13);
UPDATE `mn_zz_DocTitle` SET `menutitle` = `title`;

###############################################################################
### Task 7158  -   Preliminary tasks for online billing facility
###############################################################################
DROP TABLE IF EXISTS `mn_Invoice`;
CREATE TABLE mn_Invoice (
  id int(11) NOT NULL auto_increment,
  id_member int(11) default NULL,
  id_location int(11) default NULL,
  number int(11) NOT NULL default '0',
  date_sent date default NULL,
  date_due date default NULL,
  amount float(11,2) NOT NULL default '0.00',
  paid enum('n','y') NOT NULL default 'n',
  id_transaction int(11) NOT NULL default '0',
  payment_method enum('online','other') NOT NULL default 'other',
  sent enum('n','y') NOT NULL default 'n',
  senderror enum('n','y') NOT NULL default 'n',
  paid_date datetime default NULL,
  attachment varchar(255) NOT NULL default '',
  payment_notes text NOT NULL,
  deleted enum('n','y') NOT NULL default 'n',
  added datetime NOT NULL default '0000-00-00 00:00:00',
  `changed` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (id),
  UNIQUE KEY number (number),
  KEY id_member (id_member),
  KEY deleted (deleted),
  KEY id_location (id_location)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `mn_zz_DocItemType` (`name` ,`title` ) VALUES ('Member/Invoice', 'Member Invoices');
INSERT INTO `mn_zz_DocItemType` (`name` ,`title` ) VALUES ('Member/Invoice/Payment', 'Member Invoice Payment');
ALTER TABLE `mn_Member` ADD `billing_send_paper_copy` ENUM( 'n', 'y' ) NOT NULL AFTER `is_accepted_forum_legal_agreement`;
ALTER TABLE `mn_Location` ADD `id_pay_member` INT NULL AFTER `id_company`;
ALTER TABLE `mn_Location` ADD INDEX ( `id_pay_member` ) ;
ALTER TABLE `mn_Member` ADD `want_pay` ENUM( 'n', 'y' ) NOT NULL AFTER `billing_send_paper_copy` ;
ALTER TABLE `mn_Member` ADD `bill_to_home_address` ENUM( 'n', 'y' ) NOT NULL AFTER `industry_experience` ;

DROP TABLE IF EXISTS `mn_RequestPayExchangeDues`;
CREATE TABLE IF NOT EXISTS `mn_RequestPayExchangeDues` (
  `id` int(11) NOT NULL auto_increment,
  `id_member` int(11) NOT NULL default '0',
  `id_location` int(11) NOT NULL default '0',
  `id_realtor` int(11) default NULL,
  `approved` enum('n','y') NOT NULL default 'n',
  `access_code` char(32) NOT NULL default '',
  `added` datetime NOT NULL default '0000-00-00 00:00:00',
  `changed` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  KEY `id_member` (`id_member`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;


#----------------------------------------------------------------------
#- автоматично призначати "добровольця" для тих локацій в яких є тільки
#- один мембер
#----------------------------------------------------------------------
CREATE TEMPORARY TABLE `tmp_SmallLocation` (
      `id` int(11) NOT NULL auto_increment,
      `cnt`  int(11) NOT NULL,
      `id_member`  int(11) NOT NULL,
      PRIMARY KEY  (`id`)
);

INSERT INTO `tmp_SmallLocation`
SELECT 
    `l`.`id`, 
    (
        SELECT COUNT(*) 
        FROM `mn_Member` AS `m` 
        WHERE `m`.`id_location` = `l`.`id`
            AND `m`.`allow_mcpe_access` = 'y'
    ) AS `member_count`,
    (
        SELECT `m`.`id`
        FROM `mn_Member` AS `m` 
        WHERE `m`.`id_location` = `l`.`id`
            AND `m`.`allow_mcpe_access` = 'y'
        LIMIT 1
    ) AS `id_member`
FROM `mn_Location` AS l
WHERE 1
HAVING member_count = 1;

UPDATE `mn_Location` AS `l`
JOIN `tmp_SmallLocation` AS `tmp_l` ON `tmp_l`.`id` = `l`.`id`
JOIN `mn_Member` AS `m` ON `tmp_l`.`id_member` = `m`.`id`
SET `l`.`id_pay_member` = `m`.`id`,
    `m`.`want_pay` = 'y';
#----------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `mn_InvoiceImport` (
  `customer_id` int(255) NOT NULL default '0',
  `type` enum('exchange','mncar') NOT NULL default 'exchange',
  `customer_name` varchar(255) NOT NULL default '',
  `item_number` varchar(255) NOT NULL default '',
  `item_description` varchar(255) NOT NULL default '',
  `quantity` int(255) NOT NULL default '0',
  `unit_price` decimal(8,2) NOT NULL default '0.00',
  `address` varchar(255) NOT NULL default '',
  `address2` varchar(255) NOT NULL default '',
  `city` varchar(255) NOT NULL default '',
  `state` varchar(255) NOT NULL default '',
  `zip` varchar(255) NOT NULL default '',
  `contact_person` varchar(255) NOT NULL default '',
  `invoice_date` date NOT NULL default '0000-00-00',
  `batch_number` varchar(255) NOT NULL default '',
  `sop_number` int(255) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 ALTER TABLE `mn_Invoice` CHANGE `id_member` `id_member` INT( 11 ) NULL DEFAULT NULL ,
CHANGE `id_location` `id_location` INT( 11 ) NULL DEFAULT NULL;

 ALTER TABLE `mn_Invoice` ADD `sent` ENUM( 'n', 'y' ) NOT NULL DEFAULT 'n' AFTER `payment_method` ,
ADD `senderror` ENUM( 'n', 'y' ) NOT NULL DEFAULT 'n' AFTER `sent` ;

CREATE TABLE IF NOT EXISTS `mn_MemberTransaction` (
  `id` int(11) NOT NULL auto_increment,
  `id_member` int(11) NOT NULL default '0',
  `transaction_id` varchar(255) NOT NULL default '',
  `invoice_number` int(11) NOT NULL default '0',
  `amount` float(11,2) NOT NULL default '0.00',
  `card_number` varchar(16) NOT NULL default '',
  `added` datetime NOT NULL default '0000-00-00 00:00:00',
  `changed` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  KEY `id_member` (`id_member`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;


###############################################################################
### Member changes. Task 7516
###############################################################################
ALTER TABLE `mn_Member` ADD `billing_omit_from_invoicing` ENUM( 'y', 'n' ) NOT NULL DEFAULT 'n' AFTER `billing_send_paper_copy` ;


###############################################################################
###
###############################################################################
ALTER TABLE `mn_Announcement` ADD `alias` VARCHAR( 255 ) NOT NULL AFTER `id` ;
-- UPDATE `mn_AnnouncementCategory` SET `sorter` = '2' WHERE `mn_AnnouncementCategory`.`id` = 1 LIMIT 1 ;
-- UPDATE `mn_AnnouncementCategory` SET `sorter` = '0' WHERE `mn_AnnouncementCategory`.`id` = 3 LIMIT 1 ;


###############################################################################
### Task 7245
###############################################################################
-- UPDATE `mn_AnnouncementCategory` SET `title` = 'Member and Industry News' WHERE `mn_AnnouncementCategory`.`id` =2 LIMIT 1 ;
-- UPDATE `mn_AnnouncementCategory` SET `short_title` = 'News' WHERE `mn_AnnouncementCategory`.`id` =2 LIMIT 1 ;
-- UPDATE `mn_Announcement` SET `id_category` =2 WHERE `id_category` =1 ;
-- DELETE FROM `mn_AnnouncementCategory` WHERE `mn_AnnouncementCategory`.`id` = 1 LIMIT 1 ;

update `mn_zz_Doc` set `handler` = 'SiteDoc' where `handler` in ('SiteDocRightColumn', 'SiteDocCustomRightColumn') and `member_page` = 'n';

INSERT INTO `mn_zz_DocItemType` (`name`, `title`) VALUES ('Home', 'Home Page');
-- додати блок до домашньої сторінки

###############################################################################
### Task 7671. Here are a number of changes for online billing.
###############################################################################
INSERT INTO `mn_zz_Settings` (`id` ,`cat` ,`name` ,`label` ,`value` ,`type` ,`options` ,`added` ,`changed` ) VALUES 
(NULL , 'settings', 'billing_email', 'Billing Email', 'billing@riddler.fr-wd.com', 'emails', '', '0000-00-00 00:00:00', '0000-00-00 00:00:00');
DROP TABLE `mn_RequestPayExchangeDues`;
ALTER TABLE `mn_Location` ADD `pay_member` VARCHAR( 255 ) NOT NULL AFTER `nrds_id` ;

###############################################################################
### Task 7680. MNCAR Member Type Changes
###############################################################################
DROP TABLE IF EXISTS `mn_DMemberType`;
CREATE TABLE IF NOT EXISTS `mn_DMemberType` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `sorter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;
INSERT INTO `mn_DMemberType` (`id`, `title`, `sorter`) VALUES
(1, 'Designated Realtor', 1),
(2, 'Realtor', 2),
(3, 'Secondary', 3),
(4, 'Senior', 10),
(5, 'Paid Non-member', 9),
(7, 'Staff', 8),
(10, 'Affiliate', 4),
(11, 'Institute Affiliate', 7),
(12, 'EDC Affiliate', 5),
(13, 'Designated Affiliate', 7),
(14, 'EDC EDAM Affiliate', 6);
ALTER TABLE `mn_Member` ADD `edam_id` VARCHAR( 255 ) NOT NULL AFTER `nrds_id` ;

###############################################################################
### Task 7671#p16
###############################################################################
CREATE TABLE `tmp_BillingPerson` (
    `nrds_id` VARCHAR( 16 ) NOT NULL ,
    `name` VARCHAR( 255 ) NOT NULL ,
    UNIQUE (`nrds_id`)
) ENGINE = MYISAM;

INSERT INTO `tmp_BillingPerson` VALUES
('060301486', 'Annie Hongerholt'),
('060310133', 'Chad Aleshire'),
('90032929', 'Richard Ellefson'),
('90105052', 'Todd Breyfogle'),
('225502169', 'Bauch, Julie A'),
('496500002', 'Attn: Fritz Bredenbeck'),
('496500003', 'Kenneth Kunzman'),
('496500012', 'Jerry Teeson'),
('496500023', ''),
('496500057', 'Attn: Larry D. Anderson'),
('496500100', 'Randi Erickson'),
('496500102', ''),
('496500382', 'Mary Peterson'),
('496500387', 'Thomas Durkin'),
('496500448', 'Laurel Larsen'),
('496500459', 'Morris Fraenkel'),
('496500483', 'Ron Touchette'),
('496500563', 'Lynn Leegard'),
('496500687', ''),
('496500805', 'Lori Marcy'),
('496500839', 'Dave Noe'),
('496500840', ''),
('500500048', 'Chris Close'),
('500500150', 'Robin K Johnson'),
('502000002', 'Thomas L. Rehman'),
('502000009', 'Rolland Smaagaard'),
('502000010', 'Thomas L. Rehman'),
('502000011', ''),
('502000013', 'Thomas L. Rehman'),
('502000014', 'Coldwell Banker Burnet'),
('502000015', ''),
('502000020', 'Gary D Johnson'),
('502000025', ''),
('502000026', ''),
('502000029', 'Tony Darkenwald'),
('502000030', 'Leah Woolsey'),
('502000036', 'Arne M Rovick'),
('502000037', ''),
('502000039', 'Helen Sather'),
('502000044', 'Attn: Diane Kasprzyk - acctg'),
('502000049', 'Bruce W MacGregor'),
('502000052', 'William Ostlund'),
('502000072', 'Bud Schoening'),
('502000077', ''),
('502000079', ''),
('502000097', 'Chris Airhart'),
('502000115', 'Jeffrey B Larson'),
('502000126', 'Attn: Corporate Accounting'),
('502000134', 'Julie Fahey'),
('502000135', 'April Frahm'),
('502000136', 'DJ  Daljit Sikka'),
('502000139', ''),
('502000164', 'Paul Mitchell'),
('502000174', 'Nancy Raddohl'),
('502000182', 'Stephen J Hirsch'),
('502000190', 'Steve Bruggeman'),
('502000194', ''),
('502000234', 'William Traiser'),
('502000238', 'John E Mannillo'),
('502000277', 'Mark A Steingas'),
('502000279', 'Darrel E Gonyea'),
('502000291', ''),
('502000305', 'Gary W Lally'),
('502000314', 'Bruce Carlson'),
('502000317', 'John Laurent'),
('502000324', 'Attn: Joyce Scott'),
('502000325', ''),
('502000326', ''),
('502000332', 'Michael E Fidler'),
('502000342', 'Jeff Schoenwetter'),
('502000345', 'Attn: Lin Schirmer'),
('502000351', 'Jeffrey J Wirth'),
('502000353', 'Steve J Michel'),
('502000361', 'James M Waters'),
('502000364', 'Steven A Meyer'),
('502000373', 'Joe Smith'),
('502000374', 'Robert C Fors'),
('502000388', 'Gary L Kersten'),
('502000424', 'Patricia J Jordan'),
('502000430', 'Steve Cook'),
('502000435', 'Stan Proden'),
('502000453', 'Anthony E Feffer'),
('502000458', 'Bruce C Bermel'),
('502000501', 'Joseph M Antonucci'),
('502000506', 'Steven J Rorem'),
('502000520', 'Timothy E Mardell'),
('502000548', 'William C Tobin'),
('502000582', 'Kenneth S Sherman'),
('502000594', 'Stuart A Chazin'),
('502000597', 'Thomas Junnila'),
('502000598', 'Robert F Corson'),
('502000607', ''),
('502000610', 'Kris Hardy'),
('502000620', 'Mary Armstrong'),
('502000635', 'Laura Steffes'),
('502000638', 'Dave Jellison'),
('502000642', 'Arnold J Seltzer'),
('502000647', 'Michael H Koehler'),
('502000649', 'Larry G Ludeman'),
('502000669', 'Janene Hebert'),
('502000686', 'Thomas W Burton'),
('502000722', 'Amy Wimmer'),
('502000746', 'Darcy E Winter'),
('502000763', 'Daryl G Durheim'),
('502000772', 'Samuel S Cave'),
('502000780', 'Christopher G Willson'),
('502000816', 'Paul F Gonyea'),
('502000822', 'Bradley J Schafer'),
('502000830', 'Brian J. Zeller'),
('502000832', 'Kay Harris'),
('502000854', 'Steven L Eriksson'),
('502000856', 'Mary Jo Bailey'),
('502000871', 'Fred N Hedberg'),
('502000875', 'Robert D Kohns'),
('502000880', 'Michael J Salmen'),
('502000900', 'Robert Eastlund'),
('502000906', 'Michele Lamson'),
('502000917', 'Gary A Gabrielson'),
('502000921', 'Joseph G McKasy'),
('502000922', 'James V Maciej'),
('502000925', 'James P Gearen'),
('502000931', 'Gene Haugland'),
('502000953', ''),
('502000964', 'Steven J Heimbuch'),
('502000965', 'c/o Interbank, fsb'),
('502000966', 'Brian J Fulford'),
('502000977', 'Michael Sims'),
('502000987', 'Douglas Sailor'),
('502000997', ''),
('502000999', 'Jeffrey D Hagen'),
('502001004', 'Mark A Oehrlein'),
('502001009', 'Erik S Sorenson'),
('502001010', 'Phillip J Smith'),
('502001015', 'Eugene M Rerat'),
('502001017', 'Attn: Sara'),
('502001022', 'Daniel P Commers'),
('502001026', 'Elaine G Nordness'),
('502001028', 'Jack D Matasosky'),
('502001032', 'Ben Merriman'),
('502001036', 'George Daniels'),
('502001041', 'Denise M Currie'),
('502001048', 'Richard P Nelson'),
('502001055', 'Kristin Weise'),
('502001077', 'Thomas M Rushing'),
('502001159', ''),
('502001161', 'Ken Stabler'),
('502001182', 'Timothy P Nesvold'),
('502001200', 'Al Kline'),
('502001212', 'Paul Buchmayer'),
('502001219', 'David R. Frauenshuh'),
('502001253', 'David Lindahl'),
('502001278', 'Daniel W. Engelsma'),
('502001328', 'Jason L Messner'),
('502001395', 'Mark A Davis'),
('502001522', 'Frederick W. Gergen'),
('502001691', ''),
('502001753', 'Joseph Strauss'),
('502001760', 'Patterson, Jefferson'),
('502001766', 'J Lindsay'),
('502001770', 'John Wall'),
('502001776', 'Bruce Miller'),
('502001817', ''),
('502001824', 'Jon Sander'),
('502001829', 'Keith Sturm'),
('502001832', 'Barry B. Bosold'),
('502001834', 'Michael J. Schmitt'),
('502001891', 'Keith (Jon) Hempel'),
('502001894', 'Steve B. Wellington'),
('502001902', 'Mike Whalen'),
('502001935', 'Joseph N. Klein'),
('502001936', 'Jerry Hertel'),
('502001982', 'Ann Stahley'),
('502001984', 'Arbor Commercial Group'),
('502002073', 'Patrick W. Fischer'),
('502002079', 'Rob Davidson'),
('502002093', 'Thomas Noble'),
('502002103', 'Thomas G. Braman'),
('502002118', 'Steven R. Anderson'),
('502002119', 'Dennis S. Walsh'),
('502002124', 'Marcia A Lockman'),
('502002149', 'Jordan N Greenberg'),
('502002150', 'William H. Beard'),
('502002198', 'Bruce E. Carland'),
('502002218', 'Thomas P. Shannon'),
('502002230', ''),
('502002241', 'Gerald Norton'),
('502002246', 'Rodney A. Lee'),
('502002249', 'Frank C. Masserano'),
('502002256', 'Richard J. Jeske'),
('502002267', 'Donald L. Bachmeier'),
('502002312', 'David W. Johnson'),
('502002316', ''),
('502002334', 'Adrian Johnson'),
('502002352', 'Charles N. McCain'),
('502002409', 'Gregory Maltby'),
('502002412', 'Patricia E Ament'),
('502002420', ''),
('502002423', 'Gayle Martin'),
('502002456', ''),
('502002472', 'Peggy Schatz'),
('502002485', 'John Stainbrook'),
('502002696', 'Pfeffer, Charles J'),
('502002802', 'T C Realty'),
('502002858', 'Benjamin Ostfield'),
('502002879', 'Lund, Roar'),
('502003153', 'Mark Matasovsky'),
('502003387', ''),
('502003770', 'Traci Tomas'),
('502004120', 'Michael D Benson'),
('502004145', 'William Ask'),
('502004384', 'Carey, Colleen M'),
('502004403', 'Diebold, Dixon'),
('502004437', 'Jonathan Adam'),
('502004650', 'McRoberts Real Estate, LLC'),
('502006147', 'Michael W Ohmes'),
('502014042', 'David Sewell'),
('502029554', '10 South 5th Street'),
('502144555', 'Ashley Frank'),
('502201048', ''),
('502210047', 'Karen Larson'),
('502210082', ''),
('502210087', 'William F Smith'),
('502210092', ''),
('502210095', 'James Kramer'),
('502210121', 'John Chirhart'),
('502210153', 'Patricia A Wolf'),
('502210161', 'Patrick Gaughan'),
('502210164', 'Denny Walsh'),
('502210186', ''),
('502210190', ''),
('502210193', 'Richard Yablonsky'),
('502210201', 'Loren Hoseck'),
('502210212', 'Tom Threlkeld'),
('502210224', 'Russell Nelson'),
('502210230', 'Joel Buttenhoff'),
('502210235', 'Joe Sullivan'),
('502210237', 'Stahl Real Estate'),
('502210238', 'Bernie Frey'),
('502210240', 'Clint Miller'),
('502210242', 'Joan Grootwassink'),
('502210249', 'Paula Anderson'),
('502210252', ''),
('502210267', 'Don Gerberding'),
('502210268', 'Tom Kendall'),
('502210298', 'Michael Van Heel'),
('502210302', "Jay O'Brien"),
('502210305', 'Maggie Linvill Smith'),
('502210313', 'Mark Hoiland'),
('502210314', 'Stuart Simek'),
('502210319', 'Clinton Blaiser'),
('502210331', 'Jay Mutschler'),
('502210334', 'Bob Pfefferle'),
('502210343', 'Kim Meyer'),
('502210346', 'Todd Erager'),
('502210363', 'Mark Talkington'),
('502210371', 'Susan Weinberg'),
('502210407', 'Robert Salmen'),
('502210423', 'Clint Baer'),
('502210428', 'Stan Palmer'),
('502210433', 'Hugh Byrne'),
('502210451', 'Kenneth Streeter'),
('502210475', 'Lisa Christianson'),
('502210480', 'Greg McDonald'),
('502210506', 'Steven Fischer'),
('502210510', 'Stainbrook, Bruce'),
('502210515', 'Mary Waldrop'),
('502210518', 'Anita Johnson'),
('502210524', 'Stuart Johnson'),
('502210529', 'Becky Cederstrom'),
('502210541', 'Craig Peterson'),
('502210561', 'Paul Maenner'),
('502210573', 'Phil Kluesner'),
('502210580', 'Shelly Muelken'),
('502210592', 'James Thomas'),
('502210597', 'Michael Valentine'),
('502210601', 'Steven Fisher'),
('502210603', 'Mark Brown'),
('502210604', 'Steve Hosch'),
('502210609', 'Phillip Smith'),
('502210621', 'Gary Hugeback'),
('502210626', 'Jim DiOrio'),
('502210634', 'Leanne Anderson'),
('502210651', 'Linda Solberg'),
('502210653', 'Rob Kost'),
('502210668', 'Dean Trongard'),
('502210671', ''),
('502210676', 'Jason Tollette'),
('502210682', 'David Costello'),
('502210686', 'Chad Wiech'),
('502210693', 'Pat Borgman'),
('502210699', 'Dan Novak'),
('502210701', 'Daniel Peterson'),
('502210715', 'TJ Wilson'),
('502210716', 'James Kou Vang'),
('502210720', 'Thomas Budzynski'),
('502210759', 'Ryan Walsh'),
('502210761', 'Todd McGinley'),
('502210762', 'Peter Kordonowy'),
('502210764', 'Sherri Riling'),
('502210766', 'Attn: Andy Carlson'),
('502210767', 'Craig Kirkpatrick'),
('502210791', 'Steve Ladin'),
('502210807', 'Russ Crawford'),
('502210821', 'Mike Bobick'),
('502210824', 'John Chadwick'),
('502210825', 'David Carland'),
('502210841', ''),
('502210859', 'Dean Luski'),
('502210876', 'Donald W. Opheim'),
('502210878', 'Bank of the West'),
('502210879', 'Mark Hotzler'),
('502210881', 'Lisa Kloetzke'),
('502210882', 'Casualty Assurance'),
('502210884', 'Thomas Moe'),
('502210885', 'Trent Mayberry'),
('502210895', 'Christopher Thorsen'),
('502210904', 'Gregory Hayes'),
('502210908', 'John Donnelly'),
('502210910', ''),
('502210925', 'Access Commercial Real Estate'),
('502210926', 'Jerad Ducklow'),
('502210927', 'Henry Fischer'),
('502210930', ''),
('502210940', 'Java Properties'),
('502210946', 'St. Paul Commercial Real Estate'),
('502210949', 'Michele Foster'),
('502210956', 'Smith, Valerie'),
('502210962', 'Turner, Joe T'),
('502210963', 'Pladson, Mary Sue'),
('502210964', 'Diehl, Lisa'),
('502210968', 'Shiela DeVine'),
('502210999', 'Walker, Doug'),
('502211007', 'Norma Jaeger'),
('502211010', 'Robert Fransen'),
('502211011', ''),
('502211016', 'Ryan Stanton'),
('502211019', 'Beecham, Dave'),
('502211060', 'Jon Gossman'),
('502211079', 'Goddard, Gene'),
('502211083', 'Immen, Tom'),
('502211087', 'Harold S Ulvestad'),
('502211089', 'Frykman, Nancy'),
('502211105', 'C R Hackworthy'),
('502211110', 'Deckert, Annie'),
('502211116', 'Michael Houge'),
('502211122', 'Abel, Robert'),
('502211126', 'Weinstine, Anthony'),
('502211135', 'Wenthold, James R'),
('502211136', 'Graham, Todd'),
('502211144', 'Arthur Brown'),
('502211147', ''),
('502211150', 'Pfaff, Bradford'),
('502211162', 'Dunn, Thomas'),
('502211164', 'Ewens, Elizabeth (Betty)'),
('502211170', 'David Kravetz'),
('502211180', 'Jeffrey LaFavre'),
('502211182', 'Shannon Rusk'),
('502211183', 'Dan Regan'),
('502211188', 'Dan W Anderson'),
('502211191', 'Wayne Elam'),
('502211196', 'Stuart H Nolan'),
('502211206', 'Wayne Teig'),
('502289862', 'Kenneth Streeter'),
('503500023', 'Hamilton, John M (Mac)'),
('503502867', 'Mary Bearden'),
('503502921', 'Watts, Allan'),
('503503040', 'Bob Dubke'),
('505000006', ''),
('505000013', ''),
('505000041', 'James Callahan'),
('505000065', ''),
('505000093', 'Kevin Curtis'),
('505000105', 'Attn: Linda Sellman'),
('505000193', 'Gerald Mckinzie'),
('505000463', 'Tim Huglen'),
('505000709', 'Nicole Anderson'),
('505000731', 'Ross Malinski'),
('505000905', 'Harn, Laurence'),
('505001299', 'Foss, Brett D'),
('505500008', ''),
('505500164', 'Noel Johnson'),
('505500189', 'Jim Pflepsen'),
('505500196', 'Lou Suski'),
('506000002', 'Paul Baillon'),
('506000003', 'Scott Quittem'),
('506000004', 'Rich Robins'),
('506000005', 'Steve Lentsch'),
('506000007', 'Michael Tracy'),
('506000008', ''),
('506000009', 'Ben Lammers'),
('506000010', 'Thomas L. Rehman'),
('506000018', 'Attn: Jeri Pederson'),
('506000019', 'Kevin Adkins'),
('506000022', 'Nancy Lewis'),
('506000024', 'Barb Burke'),
('506000025', 'Michelle Goff'),
('506000035', 'Michael E Muske'),
('506000044', 'Attn: Diane Kuhlman'),
('506000050', 'James Miller'),
('506000056', 'Linda Dagenais'),
('506000084', ''),
('506000122', 'Bob Murray'),
('506000129', ''),
('506000133', 'Peter Sampair'),
('506000146', 'Tim Mannie'),
('506000153', 'Krey, Timothy'),
('506000154', 'Alan Peterson'),
('506000201', 'John Colopy'),
('506000235', ''),
('506000240', 'Ann Langer'),
('506000258', ''),
('506000346', ''),
('506000481', 'Anthony Schwartz'),
('506008257', 'Terrence Troy'),
('506009569', 'Tamra Medina'),
('506009641', 'Jeffrey Swanson'),
('506009705', 'Charles S Feather'),
('506009721', 'Christopher Fritch'),
('506009956', 'Carly Wittmer'),
('506009995', 'ATTN: MCA'),
('506010071', ''),
('506010184', 'Michael Vanderheyden'),
('506010276', 'Melanie Smith'),
('506010288', 'Jason Kramber'),
('506010346', 'Justin Fox'),
('506010376', 'David Massopust'),
('506010649', 'Chuck Cook'),
('506010888', 'Richard W Paulsen'),
('506010933', 'Anthony Lesicka'),
('506096027', 'm2 Real Estate Group'),
('777777771', '');

UPDATE mn_Location AS l 
JOIN `tmp_BillingPerson` AS bp ON l.nrds_id = bp.nrds_id
SET l.pay_member = bp.name;


#-----------------------------------------------------------------------------------------------------------------------
#- Task 8466. Data Feed Estimate
#-----------------------------------------------------------------------------------------------------------------------
--
-- Структура таблиці `mn_DCompanyFeedType`
--

CREATE TABLE IF NOT EXISTS `mn_DCompanyFeedType` (
  `id` enum('public','lease','sale','suite','property') NOT NULL default 'public',
  `title` varchar(255) NOT NULL default '',
  `sorter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Дамп даних таблиці `mn_DCompanyFeedType`
--

INSERT INTO `mn_DCompanyFeedType` (`id`, `title`, `sorter`) VALUES
('public', 'Public Feed', 0),
('lease', 'Lease Listings', 1),
('sale', 'Sale Listings', 2),
('suite', 'Suites', 3),
('property', 'Properties', 4);


CREATE TABLE IF NOT EXISTS `mn_CompanyFeedCode` (
  `id_company` int(11) NOT NULL default '0',
  `id_feed` enum('public','lease','sale','suite','property') NOT NULL default 'public',
  `code` varchar(32) NOT NULL default '',
  PRIMARY KEY  (`id_company`,`id_feed`),
  UNIQUE KEY `code` (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `mn_Company` ADD `feeds_enabled` ENUM( 'n', 'y' ) NOT NULL AFTER `website` ;

#-----------------------------------------------------------------------------------------------------------------------
#- Task 8931. Change Auto Login Feature to the Xceligent
#-----------------------------------------------------------------------------------------------------------------------
ALTER TABLE `mn_Member` ADD `cdx_access_code` CHAR( 32 ) NULL AFTER `want_pay` ;
ALTER TABLE `mn_Member` ADD UNIQUE (`cdx_access_code` );
ALTER TABLE `mn_zz_Doc` ADD `target` VARCHAR( 16 ) NOT NULL AFTER `redirect_url` ;


#-----------------------------------------------------------------------------------------------------------------------
#- Task 9084.  Exchange Dues Report Fixes
#-----------------------------------------------------------------------------------------------------------------------
INSERT INTO `mn_ExchangeDuesReportItems` (`id`, `number`, `description`, `price`, `mncar_market_id`, `member`, `first_user`, `sorter`) VALUES
(14, 'BROADCAST', 'Broadcast Email Template Quarterly Subscription', 75.00, 0, 'n', 'n', 14),
(15, 'DATAFEED', 'Data Feed Quarterly Subscription', 75.00, 0, 'n', 'n', 15);

ALTER TABLE `mn_Location` ADD `datafeeds` ENUM( 'n', 'y' ) NOT NULL AFTER `id_current_mcpe_status` ,
ADD `templates` ENUM( 'n', 'y' ) NOT NULL AFTER `datafeeds` ;

#-----------------------------------------------------------------------------------------------------------------------
#- Task 9165. Auto-login to redi-net based on the autologin that we did for Xceligent
#-----------------------------------------------------------------------------------------------------------------------
ALTER TABLE `mn_Member` ADD `redinet_access_code` VARCHAR( 32 ) NULL AFTER `cdx_access_code` ;
ALTER TABLE `mn_Member` ADD UNIQUE (`redinet_access_code`);

#-----------------------------------------------------------------------------------------------------------------------
#- Task 9707. Here are a few more changes to the site
#-----------------------------------------------------------------------------------------------------------------------
ALTER TABLE `mn_Member` ADD `show_access_tip` ENUM( 'y', 'n' ) NOT NULL AFTER `redinet_access_code` ;


#--------------------------------------------------------------------------------------------------------
# Task 10384. Sites that should have branded STS link 
#--------------------------------------------------------------------------------------------------------

INSERT INTO `mn_zz_Settings` (`name`, `cat`, `label`, `value`, `type`, `options`, `added`, `changed`) VALUES
('stsAuthLink', 'settings', 'STS Link Text', '<p>Not sure how to make the change you want or considering adding extra  functionality to your site? Let us help you. Post your question or  comment in our <a target="_blank" href="{url}">online support system</a>.</p>', 'html_editor',  '', '2010-08-25 00:00:00', '2010-09-30 17:42:59');

ALTER TABLE `mn_zz_Admin` ADD `stslogincode` CHAR( 32 ) NULL DEFAULT NULL AFTER `changed` ,
ADD INDEX ( `stslogincode` );

ALTER TABLE `mn_zz_Admin` ADD `stsloginenabled` ENUM( 'n', 'y' ) NOT NULL AFTER `changed` ,
ADD INDEX ( `stsloginenabled` );

#--------------------------------------------------------------------------------------------------------
# Task 11719. NRDS ID Updates 
#--------------------------------------------------------------------------------------------------------
UPDATE `mn_Member` 
SET `nrds_id` = `id`
WHERE id IN (4097, 4593, 4673, 5394, 5925, 6615, 6901, 7277, 7565, 7625, 8094, 9465, 9468, 9469, 9470, 9471, 9472, 9473, 9474, 9475, 
9476, 9478, 9479, 9481, 9482, 9483, 9484, 9487, 9488, 9489, 9490, 9492, 9493, 9494, 9495, 9496, 9497, 9498, 9499, 9501, 9502, 9503, 
9504, 9505, 9506, 9507, 9508, 9510, 9511, 9512, 9519, 9523, 9536, 9543, 9683, 9698, 9699, 9702, 9704, 9705, 9707);

INSERT INTO `mn_NRDS` (`id`, `id_owner`, `record_type`, `manualy_set`, `applied`, `added`) VALUES 
(4097, 4097, 'individual', 'y', 'y', NOW()),
(4593, 4593, 'individual', 'y', 'y', NOW()),
(4673, 4673, 'individual', 'y', 'y', NOW()),
(5394, 5394, 'individual', 'y', 'y', NOW()),
(5925, 5925, 'individual', 'y', 'y', NOW()),
(6615, 6615, 'individual', 'y', 'y', NOW()),
(6901, 6901, 'individual', 'y', 'y', NOW()),
(7277, 7277, 'individual', 'y', 'y', NOW()),
(7565, 7565, 'individual', 'y', 'y', NOW()),
(7625, 7625, 'individual', 'y', 'y', NOW()),
(8094, 8094, 'individual', 'y', 'y', NOW()),
(9465, 9465, 'individual', 'y', 'y', NOW()),
(9468, 9468, 'individual', 'y', 'y', NOW()),
(9469, 9469, 'individual', 'y', 'y', NOW()),
(9470, 9470, 'individual', 'y', 'y', NOW()),
(9471, 9471, 'individual', 'y', 'y', NOW()),
(9472, 9472, 'individual', 'y', 'y', NOW()),
(9473, 9473, 'individual', 'y', 'y', NOW()),
(9474, 9474, 'individual', 'y', 'y', NOW()),
(9475, 9475, 'individual', 'y', 'y', NOW()),
(9476, 9476, 'individual', 'y', 'y', NOW()),
(9478, 9478, 'individual', 'y', 'y', NOW()),
(9479, 9479, 'individual', 'y', 'y', NOW()),
(9481, 9481, 'individual', 'y', 'y', NOW()),
(9482, 9482, 'individual', 'y', 'y', NOW()),
(9483, 9483, 'individual', 'y', 'y', NOW()),
(9484, 9484, 'individual', 'y', 'y', NOW()),
(9487, 9487, 'individual', 'y', 'y', NOW()),
(9488, 9488, 'individual', 'y', 'y', NOW()),
(9489, 9489, 'individual', 'y', 'y', NOW()),
(9490, 9490, 'individual', 'y', 'y', NOW()),
(9492, 9492, 'individual', 'y', 'y', NOW()),
(9493, 9493, 'individual', 'y', 'y', NOW()),
(9494, 9494, 'individual', 'y', 'y', NOW()),
(9495, 9495, 'individual', 'y', 'y', NOW()),
(9496, 9496, 'individual', 'y', 'y', NOW()),
(9497, 9497, 'individual', 'y', 'y', NOW()),
(9498, 9498, 'individual', 'y', 'y', NOW()),
(9499, 9499, 'individual', 'y', 'y', NOW()),
(9501, 9501, 'individual', 'y', 'y', NOW()),
(9502, 9502, 'individual', 'y', 'y', NOW()),
(9503, 9503, 'individual', 'y', 'y', NOW()),
(9504, 9504, 'individual', 'y', 'y', NOW()),
(9505, 9505, 'individual', 'y', 'y', NOW()),
(9506, 9506, 'individual', 'y', 'y', NOW()),
(9507, 9507, 'individual', 'y', 'y', NOW()),
(9508, 9508, 'individual', 'y', 'y', NOW()),
(9510, 9510, 'individual', 'y', 'y', NOW()),
(9511, 9511, 'individual', 'y', 'y', NOW()),
(9512, 9512, 'individual', 'y', 'y', NOW()),
(9519, 9519, 'individual', 'y', 'y', NOW()),
(9523, 9523, 'individual', 'y', 'y', NOW()),
(9536, 9536, 'individual', 'y', 'y', NOW()),
(9543, 9543, 'individual', 'y', 'y', NOW()),
(9683, 9683, 'individual', 'y', 'y', NOW()),
(9698, 9698, 'individual', 'y', 'y', NOW()),
(9699, 9699, 'individual', 'y', 'y', NOW()),
(9702, 9702, 'individual', 'y', 'y', NOW()),
(9704, 9704, 'individual', 'y', 'y', NOW()),
(9705, 9705, 'individual', 'y', 'y', NOW()),
(9707, 9707, 'individual', 'y', 'y', NOW());

#-----------------------------------------------------------------------------------------------------------------------
# Task 12321 Duluth Area
#-----------------------------------------------------------------------------------------------------------------------

INSERT INTO `mn_DEmailGroupType` (`id`, `title`, `sorter`) VALUES (NULL, 'Duluth', '11');
UPDATE `mn_DEmailGroupType` SET `sorter` = '12' WHERE `mn_DEmailGroupType`.`id` =9 LIMIT 1 ;

#-----------------------------------------------------------------------------------------------------------------------
# 2011-03-16
# Task 12618 Broadcast Email Blocking
#-----------------------------------------------------------------------------------------------------------------------
CREATE TABLE `mn_Member2BlockedMember` (
    `id_parent` INT NOT NULL ,
    `id_blocked_member` INT NOT NULL ,
    PRIMARY KEY ( `id_parent` , `id_blocked_member` ) 
) ENGINE = MYISAM;
ALTER TABLE `mn_Member2BlockedMember` ADD INDEX ( `id_blocked_member` );
#ALTER TABLE `mn_Member` DROP `specific_senders`;

#-----------------------------------------------------------------------------------------------------------------------
# 2011-05-13
# Task 13482. Donations Page
#-----------------------------------------------------------------------------------------------------------------------
CREATE TABLE `mn_BattleDonation` (
  `id` int(11) NOT NULL auto_increment,
  `transaction_id` varchar(255) NOT NULL default '',
  `contributor_name` varchar(255) NOT NULL default '',
  `contributor_email` varchar(255) NOT NULL default '',
  `billing_name` varchar(255) NOT NULL default '',
  `billing_address` varchar(255) NOT NULL default '',
  `billing_city` varchar(255) NOT NULL default '',
  `billing_state` varchar(2) NOT NULL default '',
  `billing_zip` varchar(10) NOT NULL default '',
  `card_type` varchar(16) NOT NULL default '',
  `card_number` varchar(32) NOT NULL default '',
  `card_expiration_date` date NOT NULL default '0000-00-00',
  `card_code` varchar(4) NOT NULL default '',
  `added` datetime NOT NULL default '0000-00-00 00:00:00',
  `changed` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE `mn_BattleDonationAmount` (
  `idBattleDonation` int(11) NOT NULL default '0',
  `amount` int(11) NOT NULL default '0',
  PRIMARY KEY  (`idBattleDonation`,`amount`),
  UNIQUE KEY `amount` (`amount`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `mn_zz_DocItemType` (`name`, `title`) VALUES ('BattleDonation', 'Battle Donation');
INSERT INTO `mn_zz_Settings` (`id`, `cat`, `name`, `label`, `value`, `type`, `options`, `added`, `changed`) VALUES (NULL, 'settings', 'battle_donation_emails', 'Battle Donation Emails', 'sue@mncar.org', 'emails', '', NOW(), NOW());
ALTER TABLE `mn_zz_Settings` ADD `sorter` INT NOT NULL AFTER `options` ;
ALTER TABLE `mn_zz_Settings` ADD INDEX ( `sorter` ) ;
UPDATE `mn_zz_Settings` SET sorter = id *100;
UPDATE `mn_zz_Settings` SET `sorter` = '810' WHERE `mn_zz_Settings`.`id` =10 LIMIT 1 ;

#-----------------------------------------------------------------------------------------------------------------------
# 2011-06-03
# Task 13779. Paid Non-Member
#-----------------------------------------------------------------------------------------------------------------------
INSERT INTO `mn_DMncarLMemberType` (`id`, `title`, `sorter`) VALUES ('5', 'Paid Non-Member', '5');

INSERT INTO `mn_DMemberStatus2Type` (`type`, `id_type`, `id_status`)
SELECT 'mncar_l', `t`.`id`, `s`.`id`
FROM `mn_DMncarLMemberType` AS `t`
JOIN  `mn_DMemberStatus` AS `s` ON `s`.`id` IN (1, 2)
WHERE `t`.`id` IN (5);


#-----------------------------------------------------------------------------------------------------------------------
# 2011-06-17
# Task 13976. Foursomes - change the year
#-----------------------------------------------------------------------------------------------------------------------

SELECT 
    `efr`.`id`, 
    `efr`.`added`, 
    `e`.`id` AS `id_event`,
    `e`.`eventdate`,
    `e`.`registration_start`,
    `e`.`registration_end`,
    IF(
        DATE_FORMAT(`efr`.`added`, CONCAT(YEAR(`e`.`registration_start`), '-%m-%d %H:%i:%s')) < `e`.`registration_start`, 
        DATE_FORMAT(`efr`.`added`, CONCAT(YEAR(`e`.`registration_end`), '-%m-%d %H:%i:%s')),
        DATE_FORMAT(`efr`.`added`, CONCAT(YEAR(`e`.`registration_start`), '-%m-%d %H:%i:%s'))
    ) AS `new_added`
    
FROM `mn_EventFormReq` AS `efr`
JOIN `mn_Event` AS `e`
    ON `e`.`id` = `efr`.`id_event`
WHERE 1
    AND YEAR(`e`.`eventdate`) = 2011
    AND (
        YEAR(`efr`.`added`) < YEAR(`e`.`registration_start`)
        OR YEAR(`efr`.`added`) > YEAR(`e`.`registration_end`)
    )
-- HAVING DATE_FORMAT(`efr`.`added`, CONCAT(YEAR(`e`.`registration_start`), '-%m-%d %H:%i:%s')) != new_added
ORDER BY `e`.`eventdate` DESC;

#------
UPDATE `mn_EventFormReq` AS `efr`
JOIN `mn_Event` AS `e`
    ON `e`.`id` = `efr`.`id_event`
SET `efr`.`added` = IF(
        DATE_FORMAT(`efr`.`added`, CONCAT(YEAR(`e`.`registration_start`), '-%m-%d %H:%i:%s')) < `e`.`registration_start`, 
        DATE_FORMAT(`efr`.`added`, CONCAT(YEAR(`e`.`registration_end`), '-%m-%d %H:%i:%s')),
        DATE_FORMAT(`efr`.`added`, CONCAT(YEAR(`e`.`registration_start`), '-%m-%d %H:%i:%s'))
    )
    
WHERE 1
    AND YEAR(`e`.`eventdate`) = 2011
    AND (
        YEAR(`efr`.`added`) < YEAR(`e`.`registration_start`)
        OR YEAR(`efr`.`added`) > YEAR(`e`.`registration_end`)
    );

#-----------------------------------------------------------------------------------------------------------------------
# 
# 
#-----------------------------------------------------------------------------------------------------------------------