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

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

/**
 * @version v1.7.1
 * @signature 740428f9986da6ad85f88ec841b57bfe
 *
 *  - Migrates the email template table to two tables, groups and templates.
 *    Templates organized in a separate table by group will allow for a more
 *    extensible model for email templates.
 *
 *  - Add site page table and default templates required by the system to function.
 *
 */

DROP TABLE IF EXISTS `%TABLE_PREFIX%email_template_group`;
CREATE TABLE `%TABLE_PREFIX%email_template_group` (
  `tpl_id` int(11) NOT NULL auto_increment,
  `isactive` tinyint(1) unsigned NOT NULL default '0',
  `name` varchar(32) NOT NULL default '',
  `notes` text,
  `created` datetime NOT NULL,
  `updated` timestamp NOT NULL,
  PRIMARY KEY  (`tpl_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `%TABLE_PREFIX%email_template_group`
    SELECT `tpl_id`, `isactive`, `name`, `notes`, `created`, `updated`
    FROM `%TABLE_PREFIX%email_template`;

DROP TABLE IF EXISTS `%TABLE_PREFIX%_email_template`;
CREATE TABLE `%TABLE_PREFIX%_email_template` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `tpl_id` int(11) unsigned NOT NULL,
  `code_name` varchar(32) NOT NULL,
  `subject` varchar(255) NOT NULL default '',
  `body` text NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `template_lookup` (`tpl_id`, `code_name`)
) DEFAULT CHARSET=utf8;

INSERT INTO `%TABLE_PREFIX%_email_template`
  (`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
  SELECT `tpl_id`, 'ticket.autoresp', ticket_autoresp_subj, ticket_autoresp_body, `created`, `updated`
  FROM `%TABLE_PREFIX%email_template`;

INSERT INTO `%TABLE_PREFIX%_email_template`
  (`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
  SELECT `tpl_id`, 'ticket.autoreply', ticket_autoreply_subj, ticket_autoreply_body, `created`, `updated`
  FROM `%TABLE_PREFIX%email_template`;

INSERT INTO `%TABLE_PREFIX%_email_template`
  (`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
  SELECT `tpl_id`, 'message.autoresp', message_autoresp_subj, message_autoresp_body, `created`, `updated`
  FROM `%TABLE_PREFIX%email_template`;

INSERT INTO `%TABLE_PREFIX%_email_template`
  (`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
  SELECT `tpl_id`, 'ticket.notice', ticket_notice_subj, ticket_notice_body, `created`, `updated`
  FROM `%TABLE_PREFIX%email_template`;

INSERT INTO `%TABLE_PREFIX%_email_template`
  (`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
  SELECT `tpl_id`, 'ticket.overlimit', ticket_overlimit_subj, ticket_overlimit_body, `created`, `updated`
  FROM `%TABLE_PREFIX%email_template`;

INSERT INTO `%TABLE_PREFIX%_email_template`
  (`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
  SELECT `tpl_id`, 'ticket.reply', ticket_reply_subj, ticket_reply_body, `created`, `updated`
  FROM `%TABLE_PREFIX%email_template`;

INSERT INTO `%TABLE_PREFIX%_email_template`
  (`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
  SELECT `tpl_id`, 'ticket.alert', ticket_alert_subj, ticket_alert_body, `created`, `updated`
  FROM `%TABLE_PREFIX%email_template`;

INSERT INTO `%TABLE_PREFIX%_email_template`
  (`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
  SELECT `tpl_id`, 'message.alert', message_alert_subj, message_alert_body, `created`, `updated`
  FROM `%TABLE_PREFIX%email_template`;

INSERT INTO `%TABLE_PREFIX%_email_template`
  (`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
  SELECT `tpl_id`, 'note.alert', note_alert_subj, note_alert_body, `created`, `updated`
  FROM `%TABLE_PREFIX%email_template`;

INSERT INTO `%TABLE_PREFIX%_email_template`
  (`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
  SELECT `tpl_id`, 'assigned.alert', assigned_alert_subj, assigned_alert_body, `created`, `updated`
  FROM `%TABLE_PREFIX%email_template`;

INSERT INTO `%TABLE_PREFIX%_email_template`
  (`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
  SELECT `tpl_id`, 'transfer.alert', transfer_alert_subj, transfer_alert_body, `created`, `updated`
  FROM `%TABLE_PREFIX%email_template`;

INSERT INTO `%TABLE_PREFIX%_email_template`
  (`tpl_id`, `code_name`, `subject`, `body`, `created`, `updated`)
  SELECT `tpl_id`, 'ticket.overdue', ticket_overdue_subj, ticket_overdue_body, `created`, `updated`
  FROM `%TABLE_PREFIX%email_template`;

DROP TABLE `%TABLE_PREFIX%email_template`;
ALTER TABLE `%TABLE_PREFIX%_email_template`
    RENAME TO `%TABLE_PREFIX%email_template`;

-- pages
CREATE TABLE IF NOT EXISTS `%TABLE_PREFIX%page` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `isactive` tinyint(1) unsigned NOT NULL default '0',
  `type` enum('landing','offline','thank-you','other') NOT NULL default 'other',
  `name` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `notes` text,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) DEFAULT CHARSET=utf8;

INSERT INTO `%TABLE_PREFIX%page` (`isactive`, `type`, `name`, `body`, `notes`, `created`, `updated`) VALUES
(1, 'offline', 'Offline', '<div>\r\n<h1><span style="font-size: medium">Support Ticket System Offline</span></h1>\r\n<p>Thank you for your interest in contacting us.</p>\r\n<p>Our helpdesk is offline at the moment, please check back at a later time.</p>\r\n</div>', 'Default offline page', NOW(), NOW()),
(1, 'thank-you', 'Thank you', '<div>%{ticket.name},<br />\r\n    \r\n<p>\r\nThank you for contacting us.</p><p> A support ticket request #%{ticket.number} has been created and a representative will be getting back to you shortly if necessary.</p>\r\n          \r\n<p>Support Team </p>\r\n</div>', 'Default "thank you" page displayed after the end-user creates a web ticket.', NOW(), NOW()),
(1, 'landing', 'Landing', '<h1>Welcome to the Support Center</h1>\r\n<p>In order to streamline support requests and better serve you, we utilize a support ticket system. Every support request is assigned a unique ticket number which you can use to track the progress and responses online. For your reference we provide complete archives and history of all your support requests. A valid email address is required to submit a ticket.\r\n</p>\r\n', 'Introduction text on the landing page.', NOW(), NOW());

INSERT INTO `%TABLE_PREFIX%config` (`key`, `value`, `namespace`) VALUES
  ('landing_page_id', (SELECT `id` FROM `%TABLE_PREFIX%page` WHERE `type` = 'landing'), 'core')
, ('offline_page_id', (SELECT `id` FROM `%TABLE_PREFIX%page` WHERE `type` = 'offline'), 'core')
, ('thank-you_page_id', (SELECT `id` FROM `%TABLE_PREFIX%page` WHERE `type` = 'thank-you'), 'core');

ALTER TABLE  `%TABLE_PREFIX%help_topic`
    ADD  `page_id` INT UNSIGNED NOT NULL DEFAULT  '0' AFTER  `sla_id` ,
    ADD INDEX (  `page_id` );

ALTER TABLE  `%TABLE_PREFIX%file`
    ADD  `ft` CHAR( 1 ) NOT NULL DEFAULT  'T' AFTER `id`,
    ADD INDEX (  `ft` );

-- Finished with patch
UPDATE `%TABLE_PREFIX%config`
    SET `value` = '740428f9986da6ad85f88ec841b57bfe'
	WHERE `key` = 'schema_signature' AND `namespace` = 'core';

[+] ..
[-] 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]