-- phpMyAdmin SQL Dump -- version 4.6.6deb5ubuntu0.5 -- https://www.phpmyadmin.net/ -- -- Host: localhost:3306 -- Generation Time: Jan 08, 2021 at 05:18 AM -- Server version: 5.7.32-0ubuntu0.18.04.1 -- PHP Version: 7.2.33-1+ubuntu18.04.1+deb.sury.org+1 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 utf8mb4 */; -- -- Database: `tgsstest_product_support` -- -- -------------------------------------------------------- -- -- Table structure for table `category` -- CREATE TABLE `category` ( `category_id` int(11) NOT NULL, `category_name` varchar(255) DEFAULT NULL, `status` tinyint(11) DEFAULT '1' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `category` -- INSERT INTO `category` (`category_id`, `category_name`, `status`) VALUES (1, 'Customer support', 1), (2, 'Product support', 1), (3, 'Issue support', 1), (4, 'Billing support', 1), (5, 'Refund support', 1), (6, 'Other support', 1); -- -------------------------------------------------------- -- -- Table structure for table `conversation` -- CREATE TABLE `conversation` ( `conversation_id` int(11) NOT NULL, `ticket_id` int(11) DEFAULT NULL, `reply_msg` text, `reply_by_ticket_user_id` int(11) DEFAULT NULL, `created_on` datetime DEFAULT NULL, `updated_on` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `conversation` -- INSERT INTO `conversation` (`conversation_id`, `ticket_id`, `reply_msg`, `reply_by_ticket_user_id`, `created_on`, `updated_on`) VALUES (1, 1, '<p>sample reply</p>', 1, '2020-10-28 15:44:42', NULL); -- -------------------------------------------------------- -- -- Table structure for table `conversation_files` -- CREATE TABLE `conversation_files` ( `conversation_files_id` int(11) NOT NULL, `conversation_id` int(11) DEFAULT NULL, `ticket_id` int(11) DEFAULT NULL, `attachment_file` varchar(255) DEFAULT NULL, `created_on` datetime DEFAULT NULL, `updated_on` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `conversation_files` -- INSERT INTO `conversation_files` (`conversation_files_id`, `conversation_id`, `ticket_id`, `attachment_file`, `created_on`, `updated_on`) VALUES (1, NULL, 1, 'ticket-TIC001-1603879351-LkTY6MqFfw.jpeg', '2020-10-28 15:32:31', NULL), (2, NULL, 1, 'ticket-TIC001-1603879351-zBDpOygPAY.png', '2020-10-28 15:32:31', NULL), (3, 1, 1, 'ticket-TIC001-1603880082-rgMjQbfJCd.jpeg', '2020-10-28 15:44:42', NULL); -- -------------------------------------------------------- -- -- Table structure for table `ticket` -- CREATE TABLE `ticket` ( `ticket_id` int(11) NOT NULL, `ticket_no` varchar(100) DEFAULT NULL, `category_id` int(11) DEFAULT NULL, `ticket_user_id` int(11) DEFAULT NULL, `assignee_id` int(11) DEFAULT NULL, `subject` text, `description` text, `priority` int(11) DEFAULT NULL COMMENT '1-low,2-medium,3-high', `due_date` date DEFAULT NULL, `status` int(11) DEFAULT '1' COMMENT '1-new,2-open,3-solved,4-closed', `last_reply` datetime DEFAULT NULL, `created_on` datetime DEFAULT NULL, `created_by` varchar(100) DEFAULT NULL, `updated_on` datetime DEFAULT NULL, `updated_by` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `ticket` -- INSERT INTO `ticket` (`ticket_id`, `ticket_no`, `category_id`, `ticket_user_id`, `assignee_id`, `subject`, `description`, `priority`, `due_date`, `status`, `last_reply`, `created_on`, `created_by`, `updated_on`, `updated_by`) VALUES (1, 'TIC001', 1, 1, NULL, 'sample', 'sample', 1, NULL, 1, '2020-10-28 15:44:42', '2020-10-28 15:32:31', '1', NULL, NULL), (2, 'TIC002', 2, 1, NULL, 'fdpkfjdpf', 'ajfpkflakpfip,fajkfpa,\r\nfapfm;afpadmf,admfpdmf;amdff', 3, NULL, 1, NULL, '2020-12-23 11:37:57', '1', NULL, NULL); -- -------------------------------------------------------- -- -- Stand-in structure for view `ticket_count` -- (See below for the actual view) -- CREATE TABLE `ticket_count` ( `ticket_id` int(11) ,`ticket_attachment_count` bigint(21) ,`reply_attachment_count` bigint(21) ,`message_count` bigint(21) ); -- -------------------------------------------------------- -- -- Table structure for table `ticket_user` -- CREATE TABLE `ticket_user` ( `ticket_user_id` int(11) NOT NULL, `customer_id` int(11) DEFAULT NULL, `branch_id` int(11) DEFAULT NULL, `customer_user_id` int(11) DEFAULT NULL, `f_name` varchar(255) DEFAULT NULL, `l_name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `mobile` varchar(20) DEFAULT NULL, `photo` varchar(255) DEFAULT NULL, `admin_user_id` int(11) DEFAULT NULL, `user_type` int(11) DEFAULT NULL COMMENT '0-admin side,1-customer side' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `ticket_user` -- INSERT INTO `ticket_user` (`ticket_user_id`, `customer_id`, `branch_id`, `customer_user_id`, `f_name`, `l_name`, `email`, `mobile`, `photo`, `admin_user_id`, `user_type`) VALUES (1, 1, 1, 1, 'Cute hearts little school', '', 'tgtest2020@gmail.com', '9090909909', '', NULL, 1); -- -------------------------------------------------------- -- -- Structure for view `ticket_count` -- DROP TABLE IF EXISTS `ticket_count`; CREATE VIEW `ticket_count` AS select `ticket`.`ticket_id` AS `ticket_id`,ifnull((select count(`conversation_files`.`attachment_file`) from `conversation_files` where ((`ticket`.`ticket_id` = `conversation_files`.`ticket_id`) and isnull(`conversation_files`.`conversation_id`))),0) AS `ticket_attachment_count`,ifnull((select count(`conversation_files`.`attachment_file`) from `conversation_files` where ((`ticket`.`ticket_id` = `conversation_files`.`ticket_id`) and (`conversation_files`.`conversation_id` <> ''))),0) AS `reply_attachment_count`,ifnull((select count(`conversation`.`conversation_id`) from `conversation` where (`ticket`.`ticket_id` = `conversation`.`ticket_id`)),0) AS `message_count` from `ticket` ; -- -- Indexes for dumped tables -- -- -- Indexes for table `category` -- ALTER TABLE `category` ADD PRIMARY KEY (`category_id`); -- -- Indexes for table `conversation` -- ALTER TABLE `conversation` ADD PRIMARY KEY (`conversation_id`); -- -- Indexes for table `conversation_files` -- ALTER TABLE `conversation_files` ADD PRIMARY KEY (`conversation_files_id`); -- -- Indexes for table `ticket` -- ALTER TABLE `ticket` ADD PRIMARY KEY (`ticket_id`); -- -- Indexes for table `ticket_user` -- ALTER TABLE `ticket_user` ADD PRIMARY KEY (`ticket_user_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `category` -- ALTER TABLE `category` MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7; -- -- AUTO_INCREMENT for table `conversation` -- ALTER TABLE `conversation` MODIFY `conversation_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2; -- -- AUTO_INCREMENT for table `conversation_files` -- ALTER TABLE `conversation_files` MODIFY `conversation_files_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- AUTO_INCREMENT for table `ticket` -- ALTER TABLE `ticket` MODIFY `ticket_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- -- AUTO_INCREMENT for table `ticket_user` -- ALTER TABLE `ticket_user` MODIFY `ticket_user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2; /*!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 */;
cải xoăn