Hot_PROPERTY_TO_Intellectual_Property.sql

# Hot Property to Intellectual Property SQL migrations

# DO THE COMPANY INSERTS
INSERT INTO jos_iproperty_companies (name, street, city, postcode, fax, phone, email, website, icon, ip_source) 
SELECT name, address, suburb, postcode, fax, telephone, email, website, photo, id FROM jos_hp_companies  WHERE 1

# DO THE AGENT INSERTS
INSERT INTO jos_iproperty_agents (fname, lname, company, email, mobile, website, user_id, icon, ip_source)
SELECT SUBSTRING_INDEX(name, ' ', 1), SUBSTRING_INDEX(name, ' ', -1), company, email, mobile,  desc, user, photo, id FROM jos_hp_agents WHERE 1

# DO THE CATEGORY INSERTS
INSERT INTO jos_iproperty_categories ( title, ip_source ) 
SELECT name, id FROM jos_hp_prop_types WHERE 1;

# DO THE PROPERTY INSERTS
INSERT INTO jos_iproperty ( title, street_num, street, city, postcode, price, short_description, description, hits, ip_source )
SELECT name, SUBSTRING_INDEX(address, ' ', 1), TRIM(LEADING SUBSTRING_INDEX(address, ' ', 1) FROM address), suburb, postcode, price, intro_text, full_text, hits, id FROM jos_hp_properties;

# FOR THESE UPDATES, CHECK THE a.[column_name] AND SET THE HOT PROPERTY [field] ID ACCORDINGLY
UPDATE jos_iproperty a
SET sqft = (SELECT value FROM jos_hp_properties2 WHERE field = 20 AND property = a.ip_source)
WHERE 1;

UPDATE jos_iproperty a
SET beds = (SELECT value FROM jos_hp_properties2 WHERE field = 18 AND property = a.ip_source)
WHERE 1;

UPDATE jos_iproperty a
SET baths = (SELECT value FROM jos_hp_properties2 WHERE field = 19 AND property = a.ip_source)
WHERE 1;

UPDATE jos_iproperty a
SET lotsize = (SELECT value FROM jos_hp_properties2 WHERE field = 21 AND property = a.ip_source)
WHERE 1;

UPDATE jos_iproperty a
SET mls_id = (SELECT value FROM jos_hp_properties2 WHERE field = 22 AND property = a.ip_source)
WHERE 1;

UPDATE jos_iproperty a
SET yearbuilt = (SELECT value FROM jos_hp_properties2 WHERE field = 24 AND property = a.ip_source)
WHERE 1;

# DO THE AGENT_MID INSERTS
INSERT INTO jos_iproperty_agentmid ( agent_id, ip_source )
SELECT agent, id FROM jos_hp_properties WHERE 1;

UPDATE jos_iproperty_agentmid a
SET prop_id = (SELECT id FROM jos_iproperty WHERE ip_source = a.ip_source)
WHERE 1;

# DO THE IMAGE INSERTS
INSERT INTO jos_iproperty_images ( title, description, fname, type, ip_source )
SELECT title, `desc`, TRIM(TRAILING '.' FROM TRIM(TRAILING SUBSTRING_INDEX(`standard`, '.', -1) FROM `standard`)), CONCAT('.', SUBSTRING_INDEX(`standard`, '.', -1)), property FROM jos_hp_photos;

UPDATE jos_iproperty_images a
SET a.propid = (SELECT id FROM jos_iproperty WHERE ip_source = a.ip_source)
WHERE 1;

# DO THE PROP_MID INSERTS
INSERT INTO jos_iproperty_propmid ( cat_id, ip_source )
SELECT `type`, id FROM jos_hp_properties;

UPDATE jos_iproperty_propmid a
SET a.prop_id = (SELECT id FROM jos_iproperty WHERE ip_source = a.ip_source)
WHERE 1;