Ticket Conversion Developer Notes | NARIP Administration

Customizations
# Modified Widget look in /narip.com/wp-content/themes/arras-theme/css/styles/narip.css to add .tribe-event-date-start
# Modified /inc/tickets/index.php to support rendering the form into a post via a new plugin: Tickets Short Code
# Modified the ticket admin form to exclude body and upsell body fields and display the short code for copy/paste. Looks like: [ticketform id=”602″]

Code used for import of tickets during initial set up
## DEVELOPER NOTES AND SQL SCRIPT

# Back up database: copy to naripcom_wpnarip_20150410
# Back up specific tables: copy inc_tickets, wp_postmeta, wp_posts

# Create a start and end times for each ticket

SELECT id, CONCAT(“0”, SUBSTRING(body, LOCATE(“12:”, body), 4),”:00″) AS T
FROM inc_tickets
LIMIT 10

ALTER TABLE inc_tickets ADD event_start_time TIME NOT NULL ,
ADD event_end_time TIME NOT NULL;

UPDATE inc_tickets SET event_start_time = “00:00:00” WHERE 1;
UPDATE inc_tickets SET event_start_time = CONCAT(“0”, SUBSTRING(body, LOCATE(“1:”, body), 4),”:00″)
WHERE event_start_time < “01:00:00”;
UPDATE inc_tickets SET event_start_time = CONCAT(“0”, SUBSTRING(body, LOCATE(“2:”, body), 4),”:00″)
WHERE event_start_time < “01:00:00”;
UPDATE inc_tickets SET event_start_time = CONCAT(“0”, SUBSTRING(body, LOCATE(“3:”, body), 4),”:00″)
WHERE event_start_time < “01:00:00”;
UPDATE inc_tickets SET event_start_time = CONCAT(“0”, SUBSTRING(body, LOCATE(“4:”, body), 4),”:00″)
WHERE event_start_time < “01:00:00”;
UPDATE inc_tickets SET event_start_time = CONCAT(“0”, SUBSTRING(body, LOCATE(“5:”, body), 4),”:00″)
WHERE event_start_time < “01:00:00”;
UPDATE inc_tickets SET event_start_time = CONCAT(“0”, SUBSTRING(body, LOCATE(“6:”, body), 4),”:00″)
WHERE event_start_time < “01:00:00”;
UPDATE inc_tickets SET event_start_time = CONCAT(“0”, SUBSTRING(body, LOCATE(“7:”, body), 4),”:00″)
WHERE event_start_time < “01:00:00”;
UPDATE inc_tickets SET event_start_time = CONCAT(“0”, SUBSTRING(body, LOCATE(“8:”, body), 4),”:00″)
WHERE event_start_time < “01:00:00”;
UPDATE inc_tickets SET event_start_time = CONCAT(“0”, SUBSTRING(body, LOCATE(“9:”, body), 4),”:00″)
WHERE event_start_time < “01:00:00”;
UPDATE inc_tickets SET event_start_time = CONCAT(SUBSTRING(body, LOCATE(“12:”, body), 5),”:00″)
WHERE event_start_time < “00:00:00”;
UPDATE inc_tickets SET event_start_time = CONCAT(SUBSTRING(body, LOCATE(“11:”, body), 5),”:00″)
WHERE event_start_time < “00:00:00”;
UPDATE inc_tickets SET event_start_time = CONCAT(SUBSTRING(body, LOCATE(“10:”, body), 5),”:00″)
WHERE event_start_time < “01:00:00”;
UPDATE inc_tickets SET event_start_time = ADDTIME(event_start_time, ’12:00:00′);
UPDATE inc_tickets SET event_end_time = ADDTIME(event_start_time, ’01:00:00′);

SELECT event_start_time, event_end_time FROM inc_tickets WHERE event_start_time != “00:00:00″ GROUP BY event_start_time, event_end_time;

SELECT id, CONCAT(event_date, ” “,event_start_time) AS start, CONCAT(event_date, ” “,event_end_time) AS end
FROM inc_tickets;

# Add start and end temp colums
ALTER TABLE wp_posts ADD event_start_time DATETIME NOT NULL ,
ADD event_end_time DATETIME NOT NULL;

# Copy tickets body text and times to the master post
SELECT m.meta_id, m.post_id, m.meta_value, CONCAT(t.body, ”

[ticketform id=\””,CAST(t.id AS CHAR),”\”]”) AS b, t.body,
SUBSTRING(m.meta_value,LOCATE(‘&id=’, m.meta_value)+4) AS ticket_id,
p.id, p.post_content, p.event_start_time, p.event_end_time
FROM wp_postmeta AS m, wp_posts AS p, inc_tickets AS t
WHERE m.post_id = p.id
AND SUBSTRING(m.meta_value,LOCATE(‘&id=’, m.meta_value)+4) = t.id
AND m.meta_key = “_pprredirect_url”
AND m.meta_value LIKE “%page_id=5%”
AND p.post_type = “post”
ORDER BY post_id ASC;

# Check post 2513 with ticket 110
SELECT meta_value FROM wp_postmeta WHERE post_id = 2513 AND meta_key = “_pprredirect_url”;

UPDATE wp_postmeta AS m, wp_posts AS p, inc_tickets AS t
SET p.post_content = CONCAT(t.body, ”

[ticketform id=\””,CAST(t.id AS CHAR),”\”]”) ,
p.post_type = “tribe_events”,
p.event_start_time = CONCAT(t.event_date, ” “,t.event_start_time),
p.event_end_time = CONCAT(t.event_date, ” “,t.event_end_time)
WHERE m.post_id = p.id
AND SUBSTRING(m.meta_value,LOCATE(‘&id=’, m.meta_value)+4) = t.id
AND m.meta_key = “_pprredirect_url”
AND m.meta_value LIKE “%page_id=5%”
AND p.post_type = “post”;

# 615 rows affected

# Deactivate the redirection
SELECT m.*
FROM wp_postmeta AS m, wp_posts AS p
WHERE m.post_id = p.id
AND m.meta_key = “_pprredirect_active”
AND m.meta_value = 1
AND p.post_type = “tribe_events”;

UPDATE wp_postmeta AS m, wp_posts AS p
SET m.meta_value = 0
WHERE m.post_id = p.id
AND m.meta_key = “_pprredirect_active”
AND m.meta_value = 1
AND p.post_type = “tribe_events”;

# 495 rows affected

UPDATE wp_postmeta AS m, wp_posts AS p
SET m.meta_value = 0
WHERE m.post_id = p.id
AND m.meta_key = “_pprredirect_rewritelink”
AND m.meta_value = 1
AND p.post_type = “tribe_events”;

# Some posts were published twice – run and update dupes manually to draft status
# Add a check column at the end of the posts table.
UPDATE wp_posts
SET `check` = CONCAT(RIGHT(post_content, 6), post_title)
WHERE post_type = “tribe_events”;

SELECT ID, post_status, `check` FROM wp_posts
WHERE post_type = “tribe_events”
ORDER BY `check`

# Mark the posts as Events and set the date

SELECT m.meta_id, m.post_id, t.event_date,
SUBSTRING(m.meta_value,LOCATE(‘&id=’, m.meta_value)+4) AS ticket_id, p.id
FROM wp_postmeta AS m, wp_posts AS p, inc_tickets AS t
WHERE m.post_id = p.id
AND SUBSTRING(m.meta_value,LOCATE(‘&id=’, m.meta_value)+4) = t.id
AND m.meta_key = “_pprredirect_url”
AND m.meta_value LIKE “%page_id=5%”
AND p.post_type = “tribe_events”
ORDER BY post_id ASC;

# Create the meta data for the events
INSERT INTO wp_postmeta
(post_id, meta_key, meta_value)
SELECT id, “_EventStartDate”, event_start_time
FROM wp_posts
WHERE post_type = “tribe_events”;

INSERT INTO wp_postmeta
(post_id, meta_key, meta_value)
SELECT id, “_EventEndDate”, event_end_time
FROM wp_posts
WHERE post_type = “tribe_events”;

INSERT INTO wp_postmeta
(post_id, meta_key, meta_value)
SELECT id, “_EventOrganizerID”, “0”
FROM wp_posts
WHERE post_type = “tribe_events”;

INSERT INTO wp_postmeta
(post_id, meta_key, meta_value)
SELECT id, “_EventOrganizerID”, “events-calendar”
FROM wp_posts
WHERE post_type = “tribe_events”;

INSERT INTO wp_postmeta
(post_id, meta_key, meta_value)
SELECT id, “_EventOrganizerID”, “0”
FROM wp_posts
WHERE post_type = “tribe_events”;

INSERT INTO wp_postmeta
(post_id, meta_key, meta_value)
SELECT id, “_EventShowMapLink”, “”
FROM wp_posts
WHERE post_type = “tribe_events”;

INSERT INTO wp_postmeta
(post_id, meta_key, meta_value)
SELECT id, “_EventOrganizerID”, “0”
FROM wp_posts
WHERE post_type = “tribe_events”;

INSERT INTO wp_postmeta
(post_id, meta_key, meta_value)
SELECT id, “_EventShowMap”, “”
FROM wp_posts
WHERE post_type = “tribe_events”;

INSERT INTO wp_postmeta
(post_id, meta_key, meta_value)
SELECT id, “_EventDuration”, “32400”
FROM wp_posts
WHERE post_type = “tribe_events”;

INSERT INTO wp_postmeta
(post_id, meta_key, meta_value)
SELECT id, “_EventVenueID”, “0”
FROM wp_posts
WHERE post_type = “tribe_events”;

INSERT INTO wp_postmeta
(post_id, meta_key, meta_value)
SELECT id, “_EventCurrencySymbol”, “$”
FROM wp_posts
WHERE post_type = “tribe_events”;

INSERT INTO wp_postmeta
(post_id, meta_key, meta_value)
SELECT id, “_EventCurrencyPosition”, “prefix”
FROM wp_posts
WHERE post_type = “tribe_events”;

INSERT INTO wp_postmeta
(post_id, meta_key, meta_value)
SELECT id, “_EventCost”, “”
FROM wp_posts
WHERE post_type = “tribe_events”;

INSERT INTO wp_postmeta
(post_id, meta_key, meta_value)
SELECT id, “_EventURL”, “”
FROM wp_posts
WHERE post_type = “tribe_events”;

# 617 rows affected

# Clean up temp columns
ALTER TABLE wp_posts
DROP event_start_time,
DROP event_end_time;

# Clean up temp columns
ALTER TABLE wp_posts
DROP `check`;

# end time needs tobe the same as the start time to keep it from displaying – messed up and reset…
UPDATE wp_postmeta
SET meta_value = SUBTIME(meta_value, ’01:00:00′)
WHERE meta_key = “_EventEndDate”;
UPDATE wp_postmeta
SET meta_value = ADDTIME(meta_value, ’01:00:00′)
WHERE meta_key = “_EventEndDate”;

# Update posts to get rid of double line breaks
UPDATE wp_posts SET post_content = REPLACE(post_content,’

‘,’
‘)
WHERE post_type LIKE ‘tribe_events’;