Edit - helpdeskz.sql

Edit: helpdeskz.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `helpdeskz202`
--

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]api`
--

CREATE TABLE `[[dbprefix]]api` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `date` int(11) NOT NULL DEFAULT '0',
  `last_update` int(11) NOT NULL,
  `permissions` text,
  `ip_address` mediumtext,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `token` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]articles`
--

CREATE TABLE `[[dbprefix]]articles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `content` text,
  `category` int(11) DEFAULT '0',
  `staff_id` int(11) NOT NULL DEFAULT '0',
  `date` int(11) NOT NULL,
  `last_update` int(11) NOT NULL DEFAULT '0',
  `views` int(11) NOT NULL DEFAULT '0',
  `public` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]attachments`
--

CREATE TABLE `[[dbprefix]]attachments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `enc` varchar(200) NOT NULL,
  `filetype` varchar(200) DEFAULT NULL,
  `article_id` int(11) NOT NULL DEFAULT '0',
  `ticket_id` int(11) NOT NULL DEFAULT '0',
  `msg_id` int(11) NOT NULL DEFAULT '0',
  `filesize` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `article_id` (`article_id`),
  KEY `ticket_id` (`ticket_id`),
  KEY `msg_id` (`msg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]canned_response`
--

CREATE TABLE `[[dbprefix]]canned_response` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `message` mediumtext,
  `position` int(11) NOT NULL DEFAULT '1',
  `date` int(11) NOT NULL DEFAULT '0',
  `last_update` int(11) NOT NULL DEFAULT '0',
  `staff_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]config`
--

CREATE TABLE `[[dbprefix]]config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `logo` varchar(255) DEFAULT NULL,
  `site_name` varchar(255) DEFAULT NULL,
  `windows_title` varchar(255) DEFAULT NULL,
  `page_size` int(11) NOT NULL DEFAULT '0',
  `date_format` varchar(100) DEFAULT NULL,
  `timezone` varchar(100) DEFAULT NULL,
  `maintenance` tinyint(1) NOT NULL DEFAULT '0',
  `maintenance_message` text,
  `recaptcha` tinyint(1) NOT NULL DEFAULT '0',
  `recaptcha_sitekey` varchar(255) DEFAULT NULL,
  `recaptcha_privatekey` varchar(255) DEFAULT NULL,
  `login_attempt` int(11) NOT NULL DEFAULT '0',
  `login_attempt_minutes` int(11) NOT NULL DEFAULT '1',
  `reply_order` enum('asc','desc') NOT NULL DEFAULT 'asc',
  `tickets_page` int(11) NOT NULL DEFAULT '1',
  `tickets_replies` int(11) NOT NULL DEFAULT '1',
  `overdue_time` int(11) NOT NULL DEFAULT '48',
  `ticket_autoclose` int(11) NOT NULL DEFAULT '96',
  `ticket_attachment` tinyint(1) NOT NULL DEFAULT '0',
  `ticket_attachment_number` int(11) NOT NULL DEFAULT '1',
  `ticket_file_size` double NOT NULL DEFAULT '2',
  `ticket_file_type` mediumtext,
  `kb_articles` int(11) NOT NULL DEFAULT '4',
  `kb_maxchar` int(11) NOT NULL DEFAULT '200',
  `kb_popular` int(11) NOT NULL DEFAULT '4',
  `kb_latest` int(11) NOT NULL DEFAULT '4',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `[[dbprefix]]config`
--

INSERT INTO `[[dbprefix]]config` VALUES
(1, '', '[[site_name]]', '[[site_desc]]', 25, 'd F Y h:i a', 'America/New_York', 0, NULL, 0, '', '', 3, 5, 'desc', 15, 15, 48, 96, 1, 3, 2.5, 'a:3:{i:0;s:3:"jpg";i:1;s:3:"png";i:2;s:3:"gif";}', 2, 200, 3, 3);

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]custom_fields`
--

CREATE TABLE `[[dbprefix]]custom_fields` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(100) NOT NULL,
  `title` varchar(255) NOT NULL,
  `value` text,
  `required` tinyint(1) NOT NULL DEFAULT '0',
  `departments` mediumtext,
  `display` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]departments`
--

CREATE TABLE `[[dbprefix]]departments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dep_order` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL,
  `private` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `[[dbprefix]]departments`
--

INSERT INTO `[[dbprefix]]departments` VALUES
(1, 1, 'General', 0),
(2, 2, 'Advertising', 0),
(3, 3, 'Sales', 0);

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]emails`
--

CREATE TABLE `[[dbprefix]]emails` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `default` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(200) DEFAULT NULL,
  `email` varchar(200) NOT NULL,
  `department_id` int(11) NOT NULL DEFAULT '0',
  `created` int(11) NOT NULL DEFAULT '0',
  `last_update` int(11) NOT NULL DEFAULT '0',
  `outgoing_type` enum('php','smtp') NOT NULL,
  `smtp_host` varchar(200) DEFAULT NULL,
  `smtp_port` varchar(10) DEFAULT NULL,
  `smtp_encryption` varchar(10) DEFAULT NULL,
  `smtp_username` varchar(200) DEFAULT NULL,
  `smtp_password` varchar(200) DEFAULT NULL,
  `incoming_type` varchar(10) DEFAULT NULL,
  `imap_host` varchar(200) DEFAULT NULL,
  `imap_port` varchar(10) DEFAULT NULL,
  `imap_username` varchar(200) DEFAULT NULL,
  `imap_password` varchar(200) DEFAULT NULL,
  `imap_minutes` double NOT NULL DEFAULT '5',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `[[dbprefix]]emails`
--

INSERT INTO `[[dbprefix]]emails` VALUES
(1, 1, '[[site_name]]', '[[system_email]]', 1, [[timestamp]], 0, 'php', 'mail.gmail.com', '587', 'tls', 'username@gmail.com', '', '', '', '', '', '', 5);

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]emails_tpl`
--

CREATE TABLE `[[dbprefix]]emails_tpl` (
  `id` varchar(255) NOT NULL,
  `position` smallint(6) NOT NULL,
  `name` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `message` mediumtext NOT NULL,
  `last_update` int(11) NOT NULL DEFAULT '0',
  `status` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `[[dbprefix]]emails_tpl`
--

INSERT INTO `[[dbprefix]]emails_tpl` VALUES
('autoresponse', 4, 'New Message Autoresponse', '[#%ticket_id%] %ticket_subject%', 'Dear %client_name%,\r\nYour reply to support request #%ticket_id% has been noted.\r\nTicket Details -------------------- Ticket ID: %ticket_id% Department: %ticket_department% Status: %ticket_status% Priority: %ticket_priority% Helpdesk: %support_url%', 0, 0),
('lost_password', 2, 'Lost password confirmation', 'Password recovery for %company_name%', 'We have received a request to reset your account password for the %company_name% helpdesk (%helpdesk_url%).\r\nYour new passsword is: %client_password%\r\nThank you, %company_name% Helpdesk: %support_url%', 0, 2),
('new_ticket', 3, 'New ticket creation', '[#%ticket_id%] %ticket_subject%', 'Dear %client_name%,\r\nThank you for contacting us. This is an automated response confirming the receipt of your ticket. One of our agents will get back to you as soon as possible.\r\nFor your records, the details of the ticket are listed below. When replying, please make sure that the ticket ID is kept in the subject line to ensure that your replies are tracked appropriately.\r\nTicket ID: %ticket_id% Subject: %ticket_subject% Department: %ticket_department% Status: %ticket_status% Priority: %ticket_priority%\r\nYou can check the status of or reply to this ticket online at: %support_url%\r\nRegards, %company_name%', 0, 1),
('new_user', 1, 'Welcome email registration', 'Welcome to %company_name% helpdesk', 'Hello,\r\nThis email is confirmation that you are now registered at our helpdesk.\r\nRegistered email: %client_email% Password: %client_password%\r\nYou can visit the helpdesk to browse articles and contact us at any time:\r\n%support_url%\r\nThank you for registering!\r\n%company_name% Helpdesk: %support_url%', 0, 1),
('staff_reply', 5, 'Staff Reply', 'Re: [#%ticket_id%] %ticket_subject%', '%message% \r\n------------------------------------------------------------- Ticket Details ------------------------------------------------------------- Ticket ID: %ticket_id% Department: %ticket_department% Status: %ticket_status% Priority: %ticket_priority% Helpdesk: %support_url%', 0, 2),
('staff_ticketnotification', 6, 'New ticket notification to staff', 'New ticket notification', 'Dear %staff_name%,\r\nA new ticket has been created in department assigned for you, please login to staff panel to answer it.\r\nTicket Details ------------------- Ticket ID: %ticket_id% Department: %ticket_department% Status: %ticket_status% Priority: %ticket_priority% Helpdesk: %support_url%', 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]kb_category`
--

CREATE TABLE `[[dbprefix]]kb_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `position` int(11) NOT NULL,
  `parent` int(11) NOT NULL DEFAULT '0',
  `public` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]login_attempt`
--

CREATE TABLE `[[dbprefix]]login_attempt` (
  `ip` varchar(200) NOT NULL,
  `attempts` int(11) NOT NULL DEFAULT '0',
  `date` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `ip` (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]login_log`
--

CREATE TABLE `[[dbprefix]]login_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` int(11) NOT NULL,
  `staff_id` int(11) NOT NULL DEFAULT '0',
  `ip` varchar(255) NOT NULL,
  `agent` varchar(255) NOT NULL,
  `success` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `staff_id` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]priority`
--

CREATE TABLE `[[dbprefix]]priority` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `color` varchar(10) NOT NULL DEFAULT '#000000',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `[[dbprefix]]priority`
--

INSERT INTO `[[dbprefix]]priority` VALUES
(1, 'Low', '#8A8A8A'),
(2, 'Medium', '#000000'),
(3, 'High', '#F07D18'),
(4, 'Urgent', '#E826C6'),
(5, 'Emergency', '#E06161'),
(6, 'Critical', '#FF0000');

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]staff`
--

CREATE TABLE `[[dbprefix]]staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `fullname` varchar(100) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `token` varchar(255) DEFAULT NULL,
  `registration` int(11) NOT NULL DEFAULT '0',
  `login` int(11) NOT NULL DEFAULT '0',
  `last_login` int(11) NOT NULL DEFAULT '0',
  `department` mediumtext,
  `timezone` varchar(255) DEFAULT NULL,
  `signature` longtext,
  `avatar` varchar(200) DEFAULT NULL,
  `two_factor` varchar(255) DEFAULT NULL,
  `admin` tinyint(1) NOT NULL DEFAULT '0',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `[[dbprefix]]staff`
--

INSERT INTO `[[dbprefix]]staff` VALUES
(1, '[[admin_username]]', '[[admin_pass]]', '[[admin_realname]]', '[[admin_email]]', NULL, [[timestamp]], 0, 0, NULL, NULL, NULL, NULL, NULL, 1, 1);

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]tickets`
--

CREATE TABLE `[[dbprefix]]tickets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `department_id` int(11) NOT NULL DEFAULT '0',
  `priority_id` int(11) NOT NULL DEFAULT '0',
  `user_id` int(11) NOT NULL DEFAULT '0',
  `subject` varchar(255) NOT NULL,
  `date` int(11) NOT NULL DEFAULT '0',
  `last_update` int(11) NOT NULL DEFAULT '0',
  `status` smallint(6) NOT NULL DEFAULT '1',
  `replies` int(11) NOT NULL DEFAULT '0',
  `last_replier` tinyint(1) DEFAULT '0',
  `custom_vars` mediumtext,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]tickets_messages`
--

CREATE TABLE `[[dbprefix]]tickets_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticket_id` int(11) NOT NULL DEFAULT '0',
  `date` int(11) NOT NULL DEFAULT '0',
  `customer` int(11) NOT NULL DEFAULT '1',
  `staff_id` int(11) NOT NULL DEFAULT '0',
  `message` text,
  `ip` varchar(255) DEFAULT NULL,
  `email` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `ticket_id` (`ticket_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]ticket_notes`
--

CREATE TABLE `[[dbprefix]]ticket_notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticket_id` int(11) NOT NULL,
  `staff_id` int(11) NOT NULL,
  `date` int(11) NOT NULL,
  `message` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `[[dbprefix]]users`
--

CREATE TABLE `[[dbprefix]]users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullname` varchar(250) NOT NULL DEFAULT 'Guest',
  `email` varchar(250) NOT NULL,
  `password` varchar(150) NOT NULL,
  `registration` int(11) NOT NULL DEFAULT '0',
  `last_login` int(11) NOT NULL DEFAULT '0',
  `token` varchar(255) DEFAULT NULL,
  `timezone` varchar(200) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `status` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Cancel