ꦫꦣꦺꦤ꧀ꦄꦤ꧀ꦠꦱꦺꦤ

PATH: home / centosnipponia / public_html / ticketing.nipponia.com / osta / old / include / upgrader / streams / core

-- Port data from the ticket table
-- 1. Create form entries for each ticket
INSERT INTO `%TABLE_PREFIX%form_entry` (
    `form_id`, `object_id`, `object_type`, `sort`, `created`, `updated`)
    SELECT (SELECT id FROM %TABLE_PREFIX%form WHERE `type`='T'),
        `ticket_id`, 'T', 10, `created`, `updated`
    FROM `%TABLE_PREFIX%ticket`;

-- 2. Copy subject lines from the ticket table into form entry
INSERT INTO `%TABLE_PREFIX%form_entry_values` (
    `field_id`, `entry_id`, `value`)
    SELECT A3.`id`, A2.`id`, A1.`subject`
    FROM `%TABLE_PREFIX%ticket` A1
        INNER JOIN `%TABLE_PREFIX%form_entry` A2 ON (A2.`object_id`
                = A1.`ticket_id` AND A2.`object_type` = 'T')
        INNER JOIN `%TABLE_PREFIX%form` A4 ON (A4.`id` = A2.`form_id`)
        INNER JOIN `%TABLE_PREFIX%form_field` A3 ON (A3.`form_id`
                = A4.`id`)
    WHERE A3.`name` = 'subject';

-- 2b. Copy priority from ticket to custom form entry
INSERT INTO `%TABLE_PREFIX%form_entry_values` (
    `field_id`, `entry_id`, `value`, `value_id`)
    SELECT A3.`id`, A2.`id`, A5.`priority_desc`, A1.`priority_id`
    FROM `%TABLE_PREFIX%ticket` A1
        INNER JOIN `%TABLE_PREFIX%form_entry` A2 ON (A2.`object_id`
                = A1.`ticket_id` AND A2.`object_type` = 'T')
        INNER JOIN `%TABLE_PREFIX%form` A4 ON (A4.`id` = A2.`form_id`)
        INNER JOIN `%TABLE_PREFIX%form_field` A3 ON (A3.`form_id`
                = A4.`id`)
        INNER JOIN `%TABLE_PREFIX%ticket_priority` A5 ON (A5.`priority_id`
                = A1.`priority_id`)
    WHERE A3.`name` = 'priority';

-- 3. Create <user> accounts for everybody
--      - Start with creating email addresses for the accounts
INSERT INTO `%TABLE_PREFIX%user_email` (`address`)
    SELECT DISTINCT `email` FROM `%TABLE_PREFIX%ticket`;

--      - Then create the accounts and link the `default_email`s
INSERT INTO `%TABLE_PREFIX%user` (`name`, `default_email_id`, `created`, `updated`)
    SELECT MAX(`name`), A2.`id`, A1.`created`, A1.`updated`
    FROM `%TABLE_PREFIX%ticket` A1
        INNER JOIN `%TABLE_PREFIX%user_email` A2 ON (A1.`email` = A2.`address`)
    GROUP BY A2.`id`;

--      - Now link the user and user_email tables
ALTER TABLE `%TABLE_PREFIX%user` ADD KEY `def_eml_id` (`default_email_id`, `id`);
UPDATE `%TABLE_PREFIX%user_email` A1
    SET user_id = (
        SELECT A2.`id` FROM `%TABLE_PREFIX%user` A2
        WHERE `default_email_id` = A1.`id`);
ALTER TABLE `%TABLE_PREFIX%user` DROP INDEX `def_eml_id`;

--      - Update the ticket table
UPDATE `%TABLE_PREFIX%ticket` A1
    JOIN `%TABLE_PREFIX%user_email` A2 ON A2.`address` = A1.`email`
    SET A1.`user_id` = A2.`user_id`,
        A1.`user_email_id` = A2.`id`;

-- TODO: Move this to a client info dynamic entry
-- 4. Create form entries for each ticket
INSERT INTO `%TABLE_PREFIX%form_entry` (
    `form_id`, `object_id`, `object_type`, `sort`, `created`, `updated`)
    SELECT DISTINCT A2.`id`, `user_id`, 'U', 10, MIN(A1.`created`),
        MAX(A1.`updated`)
    FROM `%TABLE_PREFIX%ticket` A1
    JOIN `%TABLE_PREFIX%form` A2 ON (A2.`type` = 'U')
    GROUP BY `user_id`, A2.`id`;

-- 5. Copy Phone from the ticket table to section #1
INSERT INTO `%TABLE_PREFIX%form_entry_values` (
    `field_id`, `entry_id`, `value`)
    SELECT A3.`id`, A2.`id`, MAX(CONCAT(
        REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
            A1.`phone`,
            ' ', ''),
            ')', ''),
            '(', ''),
            '+', ''),
            '-', ''),
            '.', ''), 'X', A1.`phone_ext`))
    FROM `%TABLE_PREFIX%ticket` A1
        INNER JOIN `%TABLE_PREFIX%user` A5 ON (A5.`id` = A1.`user_id`)
        INNER JOIN `%TABLE_PREFIX%form_entry` A2 ON (A2.`object_id`
                = A5.`id` AND A2.`object_type` = 'U')
        INNER JOIN `%TABLE_PREFIX%form` A4 ON (A4.`id` = A2.`form_id`)
        INNER JOIN `%TABLE_PREFIX%form_field` A3 ON (A3.`form_id`
                = A4.`id`)
    WHERE A3.`name` = 'phone' AND LENGTH(A1.`phone`)
    GROUP BY A3.`id`, A2.`id`;

-- 6. Remove columns from ticket table
ALTER TABLE `%TABLE_PREFIX%ticket`
    DROP COLUMN `name`,
    DROP COLUMN `email`,
    DROP COLUMN `phone`,
    DROP COLUMN `phone_ext`,
    DROP COLUMN `subject`,
    DROP COLUMN `priority_id`;

-- 5. Cleanup ticket table with dropped varchar columns
OPTIMIZE TABLE `%TABLE_PREFIX%ticket`;

[+] ..
[-] 16fcef4a-d51f303a.patch.sql [edit]
[-] e2b4e5cb-f0d2e14d.cleanup.sql [edit]
[-] 61c9d5d7-6de40a4d.patch.sql [edit]
[-] 15af7cd3-98ae1ed2.patch.sql [edit]
[-] ed60ba20-934954de.patch.sql [edit]
[-] 32de1766-852ca89e.patch.sql [edit]
[-] 5cd0a25a-2d590ffa.task.php [edit]
[-] e2b4e5cb-f0d2e14d.task.php [edit]
[-] 740428f9-8aeda901.patch.sql [edit]
[-] 9ef33a06-8f99b8bf.patch.sql [edit]
[-] 8f99b8bf-03ff59bf.patch.sql [edit]
[-] 852ca89e-740428f9.patch.sql [edit]
[-] 9f3b454c-c0fd16f4.patch.sql [edit]
[-] 36f6b328-5cd0a25a.task.php [edit]
[-] e7038ce9-ddbe2e76.patch.sql [edit]
[-] c0fd16f4-d959a00e.patch.sql [edit]
[-] 00ff231f-9f3b454c.patch.sql [edit]
[-] 98ae1ed2-e342f869.task.php [edit]
[-] 15b30765-dd0022fb.task.php [edit]
[-] d51f303a-dad45ca2.patch.sql [edit]
[-] d51f303a-dad45ca2.task.php [edit]
[-] 6de40a4d-1b0fce99.patch.sql [edit]
[-] c37e1656-e2b4e5cb.task.php [edit]
[-] 15719536-dd0022fb.patch.sql [edit]
[-] 2e20a0eb-98ae1ed2.patch.sql [edit]
[-] 5cd0a25a-2d590ffa.cleanup.sql [edit]
[-] 435c62c3-2e7531a2.cleanup.sql [edit]
[-] 70921d5c-26fd79dc.patch.sql [edit]
[-] 98ae1ed2-e342f869.patch.sql [edit]
[-] 1da1bcba-15b30765.patch.sql [edit]
[-] 03ff59bf-b26f29a6.patch.sql [edit]
[-] add62892-c37e1656.patch.sql [edit]
[-] b26f29a6-1ee831c8.task.php [edit]
[-] 9143a511-0d6099a6.cleanup.sql [edit]
[-] 15b30765-dd0022fb.cleanup.sql [edit]
[-] e2b4e5cb-f0d2e14d.patch.sql [edit]
[-] 1b0fce99-ed60ba20.patch.sql [edit]
[-] 934954de-f1ccd3bb.task.php [edit]
[-] c00511c7-7be60a84.patch.sql [edit]
[-] 26fd79dc-00c949a6.patch.sql [edit]
[-] 1ee831c8-36f6b328.cleanup.sql [edit]
[-] e7038ce9-ddbe2e76.task.php [edit]
[-] dad45ca2-61c9d5d7.cleanup.sql [edit]
[-] 61c9d5d7-6de40a4d.task.php [edit]
[-] c2d2fabf-aa4664af.patch.sql [edit]
[-] 0d6099a6-98ad7d55.cleanup.sql [edit]
[-] 9143a511-0d6099a6.patch.sql [edit]
[-] 934b8db8-ad9d0a5f.patch.sql [edit]
[-] 98ad7d55-934b8db8.patch.sql [edit]
[-] 87d4a323-4bd47d94.patch.sql [edit]
[-] dd0022fb-f4da0c9b.patch.sql [edit]
[-] cce1ba43-e7dfe821.patch.sql [edit]
[-] 36f6b328-5cd0a25a.patch.sql [edit]
[-] dad45ca2-61c9d5d7.task.php [edit]
[-] 8aeda901-16fcef4a.task.php [edit]
[-] 8f99b8bf-03ff59bf.cleanup.sql [edit]
[-] bbb021fb-49478749.patch.sql [edit]
[-] ad9d0a5f-0ca85857.patch.sql [edit]
[-] f0d2e14d-83a22ba2.patch.sql [edit]
[-] 03ff59bf-b26f29a6.cleanup.sql [edit]
[-] f8856d56-abe9c0cb.patch.sql [edit]
[-] 0d6099a6-98ad7d55.patch.sql [edit]
[-] e342f869-c00511c7.patch.sql [edit]
[-] 00c949a6-8b923d61.patch.sql [edit]
[-] 00c949a6-8b923d61.task.php [edit]
[-] dad45ca2-61c9d5d7.patch.sql [edit]
[-] f0d2e14d-83a22ba2.task.php [edit]
[-] e6978154-cc6d1b03.patch.sql [edit]
[-] 86707325-526c601b.patch.sql [edit]
[-] c00511c7-7be60a84.cleanup.sql [edit]
[-] b26f29a6-1ee831c8.cleanup.sql [edit]
[-] 4bd47d94-e7038ce9.cleanup.sql [edit]
[-] 934954de-f1ccd3bb.patch.sql [edit]
[-] 1ee831c8-36f6b328.task.php [edit]
[-] d0e37dca-1da1bcba.patch.sql [edit]
[-] ddbe2e76-add62892.patch.sql [edit]
[-] f1ccd3bb-f5692e24.patch.sql [edit]
[-] 36f6b328-5cd0a25a.cleanup.sql [edit]
[-] b19dc97d-435c62c3.patch.sql [edit]
[-] abe9c0cb-bbb021fb.patch.sql [edit]
[-] 87d4a323-4bd47d94.task.php [edit]
[-] f1ccd3bb-f5692e24.task.php [edit]
[-] 1ee831c8-36f6b328.patch.sql [edit]
[-] cc6d1b03-914098f4.patch.sql [edit]
[-] f5692e24-4323a6a8.task.php [edit]
[-] b26f29a6-1ee831c8.patch.sql [edit]
[-] aee589ab-98ae1ed2.patch.sql [edit]
[-] 60fcbee1-f8856d56.patch.sql [edit]
[-] 8b923d61-9b5550da.patch.sql [edit]
[-] 86707325-526c601b.cleanup.sql [edit]
[-] 914098f4-87d4a323.patch.sql [edit]
[-] 435c62c3-2e7531a2.patch.sql [edit]
[-] 2d590ffa-9143a511.patch.sql [edit]
[-] 8f99b8bf-03ff59bf.task.php [edit]
[-] 49478749-c2d2fabf.patch.sql [edit]
[-] aa4664af-b19dc97d.patch.sql [edit]
[-] f5692e24-4323a6a8.patch.sql [edit]
[-] f1ccd3bb-f5692e24.cleanup.sql [edit]
[-] 0ca85857-86707325.patch.sql [edit]
[-] 4bd47d94-e7038ce9.patch.sql [edit]
[-] 26fd79dc-00c949a6.cleanup.sql [edit]
[-] 526c601b-cce1ba43.patch.sql [edit]
[-] 522e5b78-02decaa2.patch.sql [edit]
[-] 15b30765-dd0022fb.patch.sql [edit]
[-] d959a00e-32de1766.patch.sql [edit]
[-] 9b5550da-e6978154.patch.sql [edit]
[-] 4bd47d94-e7038ce9.task.php [edit]
[-] 934b8db8-ad9d0a5f.task.php [edit]
[-] f0d2e14d-83a22ba2.cleanup.sql [edit]
[-] c37e1656-e2b4e5cb.patch.sql [edit]
[-] 435c62c3-2e7531a2.task.php [edit]
[-] 5cd0a25a-2d590ffa.patch.sql [edit]
[-] 8aeda901-16fcef4a.patch.sql [edit]
[-] 98ae1ed2-e342f869.cleanup.sql [edit]
[-] 26fd79dc-00c949a6.task.php [edit]
[-] 0d6099a6-98ad7d55.task.php [edit]
[-] 2e7531a2-d0e37dca.patch.sql [edit]
[-] 4323a6a8-9ef33a06.patch.sql [edit]
[-] e7dfe821-70921d5c.patch.sql [edit]
[-] f4da0c9b-00ff231f.patch.sql [edit]
[-] 02decaa2-60fcbee1.patch.sql [edit]
[-] 7be60a84-522e5b78.patch.sql [edit]
[-] c00511c7-7be60a84.task.php [edit]
[-] 9143a511-0d6099a6.task.php [edit]
[-] 83a22ba2-5fb92bef.patch.sql [edit]
[-] a67ba35e-98ae1ed2.patch.sql [edit]