CREATE TABLE IF NOT EXISTS `mn_DExchangeAccessType` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL,
  `sorter` int(11) NOT NULL,
  `added` datetime NOT NULL,
  `changed` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


INSERT INTO `mn_DExchangeAccessType` 
(`id`, `title`, `sorter`, `added`, `changed`) VALUES 
(1, 'Exchange Agent', '0', '2012-11-30 10:30:00', '2012-11-30 10:30:00'), 
(2, 'EDC', '1', '2012-11-30 10:30:00', '2012-11-30 10:30:00'), 
(3, 'Support', '2', '2012-11-30 10:30:00', '2012-11-30 10:30:00'), 
(4, 'ADN', '3', '2012-11-30 10:30:00', '2012-11-30 10:30:00'), 
(5, 'Supporting Agent', '4', '2012-11-30 10:30:00', '2012-11-30 10:30:00'), 
(6, 'Staff', '5', '2012-11-30 10:30:00', '2012-11-30 10:30:00'), 
(7, 'Non-Member Lister', '6', '2012-11-30 10:30:00', '2012-11-30 10:30:00');

ALTER TABLE  `mn_Member` ADD  `id_exchange_access_type` INT NULL DEFAULT NULL AFTER  `status_date`;



---- Conversion:
-- 1:
UPDATE mn_Member SET id_exchange_access_type = 1
WHERE
    (
        (id_mncar_type IN (1,2,3) AND id_mncar_status IN (1, 3) AND id_mncar_l_type = 0 AND id_misc_type = 0)
        OR
        (id_mncar_type = 0 AND id_mncar_l_type = 1 AND id_mncar_l_status IN (1, 3) AND id_misc_type = 0)
        OR 
        (id_mncar_type = 0 AND id_mncar_l_type = 0 AND id_misc_type = 1 AND id_misc_status IN (1, 3))
    )
    AND allow_mcpe_access = "y"
    AND id_mcpe_access_type = 1;

-- 2
UPDATE mn_Member SET id_exchange_access_type = 2
WHERE
    id_mncar_type = 0
    AND id_mncar_l_type IN (3, 4)
    AND id_mncar_l_status IN(1, 3)
    AND id_misc_type = 0
    AND allow_mcpe_access = "y"
    AND id_mcpe_access_type = 1;

-- 3, 4
UPDATE mn_Member SET id_exchange_access_type = 3
WHERE 
    (
        (id_mncar_type = 0 AND id_mncar_l_type = 2 AND id_mncar_l_status IN (1, 3) AND id_misc_type = 4 AND id_misc_status IN (1, 3))
        OR
        (id_mncar_type = 0 AND id_mncar_l_type = 0 AND id_misc_type = 4 AND id_misc_status IN (1, 3))
    )
    AND allow_mcpe_access = "y"
    AND id_mcpe_access_type = 4;

-- 5 
UPDATE mn_Member SET id_exchange_access_type = 4
WHERE 
    id_mncar_type = 0 
    AND id_mncar_l_type = 0 
    AND id_misc_type = 1 
    AND id_misc_status IN (1, 3)
    AND allow_mcpe_access = "y"
    AND id_mcpe_access_type = 2;

-- 6
UPDATE mn_Member SET id_exchange_access_type = 5
WHERE 
    (
        (id_mncar_type IN (1, 2, 3, 4) AND id_mncar_status IN (1, 3) AND id_mncar_l_type = 0)
        OR
        (id_mncar_type = 0 AND id_mncar_l_type = 1 AND id_mncar_l_status IN (1, 3))
    )
    AND id_misc_type = 4 
    AND id_misc_status IN (1, 3)
    AND allow_mcpe_access = "y"
    AND id_mcpe_access_type = 1;

-- 7  
UPDATE mn_Member SET id_exchange_access_type = 6
WHERE 
    id_misc_type = 2 
    AND id_misc_status IN (1, 3)
    AND allow_mcpe_access = "y"
    AND id_mcpe_access_type = 3;