-- -------------
-- Mtree to SobiPro Listing Migration Scripts
-- http://www.slurpitup.com/
-- Copyright 2012, Tarik Assagai
--
-- Date: May 24 19:04:53 2012 -0500
-- --------------


-- I want to set the parent category to 2, so I'm updating the blank "lat" field to make this easier --
UPDATE jos_mt_links SET lat = 2;

-- Increase all link id's by 400 --
UPDATE jos_mt_links SET link_id = link_id +400;
UPDATE jos_mt_cfvalues SET link_id = link_id +400;
UPDATE jos_mt_cl SET link_id = link_id +400;

-- insert the records from mtree  --
insert into uizb1_sobipro_object 
(id, name, nid, approved, createdTime, metaDesc, metaKeys, owner, state, updatedTime, updater, parent) (select link_id, link_name, alias, link_published, link_created, metadesc, metakey, user_id, link_published, link_modified, user_id, lat from  jos_mt_links);

-- insert the category relations from mtree. This adds 2 as the parent category for all links.  --
insert into uizb1_sobipro_relations (id, pid) (select link_id, lat from jos_mt_links);

-- Fill in null or blank values --
update uizb1_sobipro_relations set oType  = 'entry' where oType = '';
update uizb1_sobipro_relations set position = '1' where position IS NULL;
update uizb1_sobipro_relations set validSince = '2012-05-22 00:00:00' where validSince IS NULL;
update uizb1_sobipro_relations set validUntil = '0000-00-00 00:00:00' where validUntil IS NULL;


-- set values for other fields
update uizb1_sobipro_object set name = '' where name IS NULL;
update uizb1_sobipro_object set approved = '1' where approved IS NULL;
update uizb1_sobipro_object set confirmed = '0' where confirmed IS NULL;
update uizb1_sobipro_object set cout = '0' where cout IS NULL;
update uizb1_sobipro_object set coutTime = '0000-00-00 00:00:00' where coutTime IS NULL;
update uizb1_sobipro_object set createdTime = '2012-05-22 00:00:00' where createdTime IS NULL;
update uizb1_sobipro_object set defURL = '' where defURL IS NULL;
update uizb1_sobipro_object set metaDesc = '' where metaDesc IS NULL;
update uizb1_sobipro_object set metaKeys = '' where metaKeys IS NULL;
update uizb1_sobipro_object set options = '' where options IS NULL;
update uizb1_sobipro_object set oType = 'entry' where oType IS NULL;
update uizb1_sobipro_object set owner = '42' where owner IS NULL;
update uizb1_sobipro_object set ownerIP = '::1' where ownerIP IS NULL;
update uizb1_sobipro_object set params = '' where params IS NULL;
update uizb1_sobipro_object set stateExpl = '' where stateExpl IS NULL;

update uizb1_sobipro_object set validSince = '2012-05-22 00:00:00' where validSince IS NULL;
update uizb1_sobipro_object set validUntil = '0000-00-00 00:00:00' where validUntil IS NULL;
update uizb1_sobipro_object set updatedTime = '2012-05-22 00:00:00' where updatedTime IS NULL;
update uizb1_sobipro_object set updaterIP = '::1' where updaterIP IS NULL;
update uizb1_sobipro_object set version = '1' where version = '0';

update uizb1_sobipro_object set nid = replace(nid,'-','_');





-- standard listing field values --

-- listing name
insert into uizb1_sobipro_field_data (sid, baseData) (select link_id, link_name from jos_mt_links);
update uizb1_sobipro_field_data set fid = '1' where fid = '0';

-- address
insert into uizb1_sobipro_field_data (sid, baseData) (select link_id, address from jos_mt_links);
update uizb1_sobipro_field_data set fid = '35' where fid = '0';

-- city
insert into uizb1_sobipro_field_data (sid, baseData) (select link_id, city from jos_mt_links);
update uizb1_sobipro_field_data set fid = '3' where fid = '0';

-- state
insert into uizb1_sobipro_field_data (sid, baseData) (select link_id, state from jos_mt_links);
update uizb1_sobipro_field_data set fid = '36' where fid = '0';

-- postcode
insert into uizb1_sobipro_field_data (sid, baseData) (select link_id, postcode from jos_mt_links);
update uizb1_sobipro_field_data set fid = '2' where fid = '0';

-- phone
insert into uizb1_sobipro_field_data (sid, baseData) (select link_id, telephone from jos_mt_links);
update uizb1_sobipro_field_data set fid = '5' where fid = '0';

-- fax
insert into uizb1_sobipro_field_data (sid, baseData) (select link_id, fax from jos_mt_links);
update uizb1_sobipro_field_data set fid = '6' where fid = '0';

-- email
insert into uizb1_sobipro_field_data (sid, baseData) (select link_id, email from jos_mt_links);
update uizb1_sobipro_field_data set fid = '7' where fid = '0';


-- website
insert into uizb1_sobipro_field_data (sid, baseData) (select link_id, website from jos_mt_links);
update uizb1_sobipro_field_data set fid = '37' where fid = '0';




-- For this section, you will need to add lines for your custom fields. The two below correspond to my mtree custom fields. Field ID 29 and 30 --

-- contact person. The mtree custom field with ID 29 is the contact person. The contact person field in Sobi is 4
insert into uizb1_sobipro_field_data (sid, baseData) (select link_id, value from jos_mt_cfvalues where cf_id = '29');
update uizb1_sobipro_field_data set fid = '4' where fid = '0';


-- contact person Position
insert into uizb1_sobipro_field_data (sid, baseData) (select link_id, value from jos_mt_cfvalues where cf_id = '30');
update uizb1_sobipro_field_data set fid = '38' where fid = '0';


-- Fill in the blanks --

update uizb1_sobipro_field_data set publishUp = '0000-00-00 00:00:00' where publishUp IS NULL;
update uizb1_sobipro_field_data set publishDown = '0000-00-00 00:00:00' where publishDown IS NULL;
update uizb1_sobipro_field_data set section = '1' where section = '0';


-- update all misc fields for each item
update uizb1_sobipro_field_data set lang = 'en-GB' where lang = '';

update uizb1_sobipro_field_data set enabled = '1' where enabled = '0';
update uizb1_sobipro_field_data set approved = '1' where approved IS NULL;
update uizb1_sobipro_field_data set confirmed = '0' where confirmed IS NULL;
update uizb1_sobipro_field_data set createdTime = '2012-05-22 00:00:00' where createdTime IS NULL;
update uizb1_sobipro_field_data set createdBy = '0' where createdBy IS NULL;
update uizb1_sobipro_field_data set createdIP = '0' where createdIP IS NULL;
update uizb1_sobipro_field_data set updatedTime = '0000-00-00 00:00:00' where updatedTime IS NULL;
update uizb1_sobipro_field_data set updatedBy = '0' where updatedBy IS NULL;
update uizb1_sobipro_field_data set updatedBy = '42' where updatedBy IS NULL;
update uizb1_sobipro_field_data set updatedIP = '::1' where updatedIP IS NULL;
update uizb1_sobipro_field_data set editLimit = '2' where editLimit IS NULL;



-- Listing Category Relationships --
-- set the id eand parent id from the CL table
insert into uizb1_sobipro_relations (id, pid) (select link_id, cat_id from jos_mt_cl);
update uizb1_sobipro_relations set oType  = 'entry' where oType = '';
update uizb1_sobipro_relations set position = '1' where position IS NULL;
update uizb1_sobipro_relations set validSince = '2012-05-22 00:00:00' where validSince IS NULL;
update uizb1_sobipro_relations set validUntil = '0000-00-00 00:00:00' where validUntil IS NULL;


-- re- ID the website field --
-- delete from uizb1_sobipro_field_data where fid = '11';
-- update uizb1_sobipro_field_data set fid = '37' where fid = '11';