database.php 5.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137
  1. <?php
  2. /**
  3. * Contains the SQL for the tickets management plugin database structure
  4. */
  5. $table = Database::get_main_table(TABLE_SUPPORT_ASSIGNED_LOG);
  6. $sql = "CREATE TABLE IF NOT EXISTS ".$table." (
  7. iid int unsigned not null,
  8. ticket_id int UNSIGNED DEFAULT NULL,
  9. user_id int UNSIGNED DEFAULT NULL,
  10. assigned_date datetime DEFAULT NULL,
  11. sys_insert_user_id int UNSIGNED DEFAULT NULL,
  12. PRIMARY KEY PK_ticket_assigned_log (iid),
  13. KEY FK_ticket_assigned_log (ticket_id))";
  14. Database::query($sql);
  15. $table = Database::get_main_table(TABLE_SUPPORT_CATEGORY);
  16. $sql = "CREATE TABLE IF NOT EXISTS ".$table." (
  17. iid int unsigned not null,
  18. category_id char(3) NOT NULL,
  19. project_id char(3) NOT NULL,
  20. name varchar(100) NOT NULL,
  21. description varchar(255) NOT NULL,
  22. total_tickets int UNSIGNED NOT NULL DEFAULT '0',
  23. course_required char(1) NOT NULL,
  24. sys_insert_user_id int UNSIGNED DEFAULT NULL,
  25. sys_insert_datetime datetime DEFAULT NULL,
  26. sys_lastedit_user_id int UNSIGNED DEFAULT NULL,
  27. sys_lastedit_datetime datetime DEFAULT NULL,
  28. PRIMARY KEY (iid))";
  29. Database::query($sql);
  30. $table = Database::get_main_table(TABLE_SUPPORT_MESSAGE);
  31. $sql = "CREATE TABLE IF NOT EXISTS ".$table." (
  32. message_id int UNSIGNED NOT NULL,
  33. ticket_id int UNSIGNED NOT NULL,
  34. subject varchar(150) DEFAULT NULL,
  35. message text NOT NULL,
  36. status char(3) NOT NULL,
  37. ip_address varchar(16) DEFAULT NULL,
  38. sys_insert_user_id int UNSIGNED DEFAULT NULL,
  39. sys_insert_datetime datetime DEFAULT NULL,
  40. sys_lastedit_user_id int UNSIGNED DEFAULT NULL,
  41. sys_lastedit_datetime datetime DEFAULT NULL,
  42. PRIMARY KEY (message_id),
  43. KEY FK_tick_message (ticket_id) )";
  44. Database::query($sql);
  45. $table = Database::get_main_table(TABLE_SUPPORT_MESSAGE_ATTACHMENTS);
  46. $sql = "CREATE TABLE IF NOT EXISTS ".$table." (
  47. iid int unsigned not null,
  48. message_attch_id char(2) NOT NULL,
  49. message_id char(2) NOT NULL,
  50. ticket_id int UNSIGNED NOT NULL,
  51. path varchar(255) NOT NULL,
  52. filename varchar(255) NOT NULL,
  53. size varchar(25) DEFAULT NULL,
  54. sys_insert_user_id int UNSIGNED DEFAULT NULL,
  55. sys_insert_datetime datetime DEFAULT NULL,
  56. sys_lastedit_user_id int UNSIGNED DEFAULT NULL,
  57. sys_lastedit_datetime datetime DEFAULT NULL,
  58. PRIMARY KEY (iid),
  59. KEY ticket_message_id_fk (message_id))";
  60. Database::query($sql);
  61. $table = Database::get_main_table(TABLE_SUPPORT_PRIORITY);
  62. $sql = "CREATE TABLE IF NOT EXISTS ".$table." (
  63. iid int unsigned not null,
  64. priority_id char(3) NOT NULL,
  65. priority varchar(20) DEFAULT NULL,
  66. priority_desc varchar(250) DEFAULT NULL,
  67. priority_color varchar(25) DEFAULT NULL,
  68. priority_urgency tinyint DEFAULT NULL,
  69. sys_insert_user_id int UNSIGNED DEFAULT NULL,
  70. sys_insert_datetime datetime DEFAULT NULL,
  71. sys_lastedit_user_id int UNSIGNED DEFAULT NULL,
  72. sys_lastedit_datetime datetime DEFAULT NULL,
  73. PRIMARY KEY (iid))";
  74. Database::query($sql);
  75. $table = Database::get_main_table(TABLE_SUPPORT_PROJECT);
  76. $sql = "CREATE TABLE IF NOT EXISTS ".$table." (
  77. iid int unsigned not null,
  78. project_id char(3) NOT NULL,
  79. name varchar(50) DEFAULT NULL,
  80. description varchar(250) DEFAULT NULL,
  81. email varchar(50) DEFAULT NULL,
  82. other_area tinyint NOT NULL DEFAULT '0',
  83. sys_insert_user_id int UNSIGNED DEFAULT NULL,
  84. sys_insert_datetime datetime DEFAULT NULL,
  85. sys_lastedit_user_id int UNSIGNED DEFAULT NULL,
  86. sys_lastedit_datetime datetime DEFAULT NULL,
  87. PRIMARY KEY (iid))";
  88. Database::query($sql);
  89. $table = Database::get_main_table(TABLE_SUPPORT_STATUS);
  90. $sql = "CREATE TABLE IF NOT EXISTS ".$table." (
  91. iid int unsigned not null,
  92. status_id char(3) NOT NULL,
  93. name varchar(100) NOT NULL,
  94. description varchar(255) DEFAULT NULL,
  95. PRIMARY KEY (iid))";
  96. Database::query($sql);
  97. $table = Database::get_main_table(TABLE_SUPPORT_TICKET);
  98. $sql = "CREATE TABLE IF NOT EXISTS ".$table." (
  99. ticket_id int UNSIGNED NOT NULL AUTO_INCREMENT,
  100. ticket_code char(12) DEFAULT NULL,
  101. project_id char(3) DEFAULT NULL,
  102. category_id char(3) NOT NULL,
  103. priority_id char(3) NOT NULL,
  104. course_id int UNSIGNED NOT NULL,
  105. request_user int UNSIGNED NOT NULL,
  106. personal_email varchar(150) DEFAULT NULL,
  107. assigned_last_user int UNSIGNED NOT NULL DEFAULT '0',
  108. status_id char(3) NOT NULL,
  109. total_messages int UNSIGNED NOT NULL DEFAULT '0',
  110. keyword varchar(250) DEFAULT NULL,
  111. source char(3) NOT NULL,
  112. start_date datetime NOT NULL,
  113. end_date datetime DEFAULT NULL,
  114. sys_insert_user_id int UNSIGNED DEFAULT NULL,
  115. sys_insert_datetime datetime DEFAULT NULL,
  116. sys_lastedit_user_id int UNSIGNED DEFAULT NULL,
  117. sys_lastedit_datetime datetime DEFAULT NULL,
  118. PRIMARY KEY (ticket_id),
  119. UNIQUE KEY UN_ticket_code (ticket_code),
  120. KEY FK_ticket_priority (priority_id),
  121. KEY FK_ticket_category (project_id,category_id))";
  122. Database::query($sql);
  123. // Menu main tabs
  124. //$table = Database::get_main_table('ticket_ticket');
  125. //$sql = "INSERT INTO settings_current
  126. //(variable, subkey, type, category, selected_value, title, comment, scope, subkeytext, access_url_changeable)
  127. //VALUES
  128. //('show_tabs', 'tickets', 'checkbox', 'Platform', 'true', 'ShowTabsTitle', 'ShowTabsComment', NULL, 'TabsTickets', 1)";
  129. //Database::query($sql);