structure.sql 111 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735173617371738173917401741174217431744174517461747174817491750175117521753175417551756175717581759176017611762176317641765176617671768176917701771177217731774177517761777177817791780178117821783178417851786178717881789179017911792179317941795179617971798179918001801180218031804180518061807180818091810181118121813181418151816181718181819182018211822182318241825182618271828182918301831183218331834183518361837183818391840184118421843184418451846184718481849185018511852185318541855185618571858185918601861186218631864186518661867186818691870187118721873187418751876187718781879188018811882188318841885188618871888188918901891189218931894189518961897189818991900190119021903190419051906190719081909191019111912191319141915191619171918191919201921192219231924192519261927192819291930193119321933193419351936193719381939194019411942194319441945194619471948194919501951195219531954195519561957195819591960196119621963196419651966196719681969197019711972197319741975197619771978197919801981198219831984198519861987198819891990199119921993199419951996199719981999200020012002200320042005200620072008200920102011201220132014201520162017201820192020202120222023202420252026202720282029203020312032203320342035203620372038203920402041204220432044204520462047204820492050205120522053205420552056205720582059206020612062206320642065206620672068206920702071207220732074207520762077207820792080208120822083208420852086208720882089209020912092209320942095209620972098209921002101210221032104210521062107210821092110211121122113
  1. SET NAMES utf8mb4;
  2. SET FOREIGN_KEY_CHECKS = 0;
  3. -- ----------------------------
  4. -- Table structure for t_achievements
  5. -- ----------------------------
  6. DROP TABLE IF EXISTS `t_achievements`;
  7. CREATE TABLE `t_achievements` (
  8. `achievements_id` int(11) NOT NULL AUTO_INCREMENT,
  9. `total_goal` double(22, 0) NOT NULL,
  10. `no_customer_tag` double(5, 2) NULL DEFAULT 0.00,
  11. `customer_tag` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  12. `ctime` datetime(0) NULL DEFAULT NULL,
  13. `utime` datetime(0) NULL DEFAULT NULL,
  14. `dtime` datetime(0) NULL DEFAULT NULL,
  15. PRIMARY KEY (`achievements_id`) USING BTREE
  16. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  17. -- ----------------------------
  18. -- Table structure for t_achievements_group
  19. -- ----------------------------
  20. DROP TABLE IF EXISTS `t_achievements_group`;
  21. CREATE TABLE `t_achievements_group` (
  22. `achievements_group_id` int(11) NOT NULL AUTO_INCREMENT,
  23. `group_total_goal` double(22, 0) NOT NULL,
  24. `achievements_group_title` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  25. `achievements_group_users` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  26. PRIMARY KEY (`achievements_group_id`) USING BTREE
  27. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  28. -- ----------------------------
  29. -- Table structure for t_affair
  30. -- ----------------------------
  31. DROP TABLE IF EXISTS `t_affair`;
  32. CREATE TABLE `t_affair` (
  33. `affair_id` int(11) NOT NULL AUTO_INCREMENT,
  34. `affair_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  35. `affair_icon` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  36. `affair_tablename` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  37. `affair_is_buildin` tinyint(255) NULL DEFAULT NULL,
  38. `affair_group_id` int(11) NULL DEFAULT NULL,
  39. `version` int(11) NULL DEFAULT NULL,
  40. `lock_time` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  41. `lock_user` int(11) NULL DEFAULT NULL,
  42. PRIMARY KEY (`affair_id`) USING BTREE,
  43. INDEX `affair_group_id`(`affair_group_id`) USING BTREE,
  44. CONSTRAINT `t_affair_ibfk_1` FOREIGN KEY (`affair_group_id`) REFERENCES `t_affair_group` (`affair_group_id`) ON DELETE SET NULL ON UPDATE CASCADE
  45. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  46. -- ----------------------------
  47. -- Table structure for t_affair_bar
  48. -- ----------------------------
  49. DROP TABLE IF EXISTS `t_affair_bar`;
  50. CREATE TABLE `t_affair_bar` (
  51. `bar_id` int(11) NOT NULL AUTO_INCREMENT,
  52. `user_id` int(11) NOT NULL,
  53. `affairIds` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  54. `utime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  55. `affair_category` tinyint(1) NULL DEFAULT 0,
  56. PRIMARY KEY (`bar_id`) USING BTREE,
  57. INDEX `user_id`(`user_id`) USING BTREE,
  58. CONSTRAINT `t_affair_bar_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
  59. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;
  60. -- ----------------------------
  61. -- Table structure for t_affair_calculation
  62. -- ----------------------------
  63. DROP TABLE IF EXISTS `t_affair_calculation`;
  64. CREATE TABLE `t_affair_calculation` (
  65. `calculation_id` int(11) NOT NULL AUTO_INCREMENT,
  66. `affair_id` int(11) NOT NULL,
  67. `calculation_formula` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  68. `calculation_formula_title` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  69. `calculation_result` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  70. `calculation_result_title` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  71. `calculation_result_accuracy` tinyint(1) NULL DEFAULT 0,
  72. `all_algebras` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  73. `calculation_steps` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL,
  74. PRIMARY KEY (`calculation_id`) USING BTREE,
  75. INDEX `affair_id`(`affair_id`) USING BTREE,
  76. CONSTRAINT `t_affair_calculation_ibfk_1` FOREIGN KEY (`affair_id`) REFERENCES `t_affair` (`affair_id`) ON DELETE CASCADE ON UPDATE CASCADE
  77. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  78. -- ----------------------------
  79. -- Table structure for t_affair_frequently
  80. -- ----------------------------
  81. DROP TABLE IF EXISTS `t_affair_frequently`;
  82. CREATE TABLE `t_affair_frequently` (
  83. `frequently_id` int(11) NOT NULL AUTO_INCREMENT,
  84. `user_id` int(11) NOT NULL,
  85. `affair_id` int(11) NOT NULL,
  86. `ctime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  87. PRIMARY KEY (`frequently_id`) USING BTREE,
  88. INDEX `affair_id`(`affair_id`) USING BTREE,
  89. INDEX `user_id`(`user_id`) USING BTREE,
  90. CONSTRAINT `t_affair_frequently_ibfk_1` FOREIGN KEY (`affair_id`) REFERENCES `t_affair` (`affair_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  91. CONSTRAINT `t_affair_frequently_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
  92. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;
  93. -- ----------------------------
  94. -- Table structure for t_affair_group
  95. -- ----------------------------
  96. DROP TABLE IF EXISTS `t_affair_group`;
  97. CREATE TABLE `t_affair_group` (
  98. `affair_group_id` int(11) NOT NULL AUTO_INCREMENT,
  99. `affair_group_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  100. `affair_group_notes` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  101. `status` int(11) NULL DEFAULT 99,
  102. `ctime` datetime(0) NULL DEFAULT NULL,
  103. `utime` datetime(0) NULL DEFAULT NULL,
  104. `dtime` datetime(0) NULL DEFAULT NULL,
  105. PRIMARY KEY (`affair_group_id`) USING BTREE
  106. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  107. -- ----------------------------
  108. -- Table structure for t_affair_modify_record
  109. -- ----------------------------
  110. DROP TABLE IF EXISTS `t_affair_modify_record`;
  111. CREATE TABLE `t_affair_modify_record` (
  112. `record_id` int(11) NOT NULL AUTO_INCREMENT,
  113. `data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  114. `affair_id` int(11) NOT NULL,
  115. `user_id` int(11) NOT NULL,
  116. `record_data` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  117. `ctime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  118. `type` tinyint(1) NULL DEFAULT 0,
  119. PRIMARY KEY (`record_id`) USING BTREE,
  120. INDEX `user_id`(`user_id`) USING BTREE,
  121. CONSTRAINT `t_affair_modify_record_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
  122. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;
  123. -- ----------------------------
  124. -- Table structure for t_affair_rule
  125. -- ----------------------------
  126. DROP TABLE IF EXISTS `t_affair_rule`;
  127. CREATE TABLE `t_affair_rule` (
  128. `rule_id` int(11) NOT NULL AUTO_INCREMENT,
  129. `affair_id` int(11) NULL DEFAULT NULL,
  130. `sheet_id` int(11) NULL DEFAULT NULL,
  131. `user_id` int(11) NULL DEFAULT NULL,
  132. `rule_allow_use` tinyint(1) NULL DEFAULT 0,
  133. `rule_allow_view` tinyint(1) NULL DEFAULT 0,
  134. PRIMARY KEY (`rule_id`) USING BTREE,
  135. INDEX `sheet_id`(`sheet_id`) USING BTREE,
  136. CONSTRAINT `t_affair_rule_ibfk_1` FOREIGN KEY (`sheet_id`) REFERENCES `t_affair_sheet` (`sheet_id`) ON DELETE CASCADE ON UPDATE RESTRICT
  137. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  138. -- ----------------------------
  139. -- Table structure for t_affair_sheet
  140. -- ----------------------------
  141. DROP TABLE IF EXISTS `t_affair_sheet`;
  142. CREATE TABLE `t_affair_sheet` (
  143. `sheet_id` int(11) NOT NULL AUTO_INCREMENT,
  144. `sheet_prev_id` int(11) NULL DEFAULT NULL,
  145. `sheet_next_id` int(11) NULL DEFAULT NULL,
  146. `affair_id` int(11) NOT NULL,
  147. `sheet_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  148. `sheet_tablename` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  149. `sheet_rule_use_option` tinyint(1) NULL DEFAULT NULL,
  150. `sheet_rule_view_option` tinyint(1) NULL DEFAULT NULL,
  151. `sheet_join_order` tinyint(1) NULL DEFAULT 0,
  152. PRIMARY KEY (`sheet_id`) USING BTREE,
  153. INDEX `affair_id`(`affair_id`) USING BTREE,
  154. CONSTRAINT `t_affair_sheet_ibfk_1` FOREIGN KEY (`affair_id`) REFERENCES `t_affair` (`affair_id`) ON DELETE CASCADE ON UPDATE CASCADE
  155. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  156. -- ----------------------------
  157. -- Table structure for t_affair_sheet_column
  158. -- ----------------------------
  159. DROP TABLE IF EXISTS `t_affair_sheet_column`;
  160. CREATE TABLE `t_affair_sheet_column` (
  161. `sheet_column_id` int(11) NOT NULL AUTO_INCREMENT,
  162. `sheet_column_pid` int(11) NULL DEFAULT 0,
  163. `column_id` int(11) NULL DEFAULT NULL,
  164. `sheet_id` int(11) NULL DEFAULT NULL,
  165. `sheet_group_id` int(11) NULL DEFAULT NULL,
  166. `column_is_require` tinyint(1) NULL DEFAULT 0,
  167. `column_is_summary` tinyint(1) NULL DEFAULT 0,
  168. `column_order` int(11) NULL DEFAULT 0,
  169. PRIMARY KEY (`sheet_column_id`) USING BTREE,
  170. INDEX `sheet_id`(`sheet_id`) USING BTREE,
  171. INDEX `system_column_id`(`column_id`) USING BTREE,
  172. CONSTRAINT `t_affair_sheet_column_ibfk_1` FOREIGN KEY (`sheet_id`) REFERENCES `t_affair_sheet` (`sheet_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  173. CONSTRAINT `t_affair_sheet_column_ibfk_2` FOREIGN KEY (`column_id`) REFERENCES `t_affair_system_column` (`column_id`) ON DELETE CASCADE ON UPDATE CASCADE
  174. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  175. -- ----------------------------
  176. -- Table structure for t_affair_sheet_column_group
  177. -- ----------------------------
  178. DROP TABLE IF EXISTS `t_affair_sheet_column_group`;
  179. CREATE TABLE `t_affair_sheet_column_group` (
  180. `sheet_group_id` int(11) NOT NULL AUTO_INCREMENT,
  181. `sheet_group_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  182. `sheet_id` int(11) NULL DEFAULT NULL,
  183. PRIMARY KEY (`sheet_group_id`) USING BTREE,
  184. INDEX `sheet_id`(`sheet_id`) USING BTREE,
  185. CONSTRAINT `t_affair_sheet_column_group_ibfk_1` FOREIGN KEY (`sheet_id`) REFERENCES `t_affair_sheet` (`sheet_id`) ON DELETE CASCADE ON UPDATE RESTRICT
  186. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  187. -- ----------------------------
  188. -- Table structure for t_affair_system_column
  189. -- ----------------------------
  190. DROP TABLE IF EXISTS `t_affair_system_column`;
  191. CREATE TABLE `t_affair_system_column` (
  192. `column_id` int(11) NOT NULL AUTO_INCREMENT,
  193. `column_pid` int(11) NULL DEFAULT 0,
  194. `group_id` int(11) NULL DEFAULT NULL,
  195. `column_order` int(11) NULL DEFAULT NULL,
  196. `column_title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  197. `column_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  198. `column_unit` varchar(500) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '[]',
  199. `column_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  200. `column_control` tinyint(1) NULL DEFAULT 0,
  201. `column_rule` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  202. `column_is_virtual` tinyint(1) NULL DEFAULT 0,
  203. `column_unit_symbiosis` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  204. `column_detachable` tinyint(1) NULL DEFAULT 0,
  205. `column_branch_calculation` text COLLATE utf8_bin DEFAULT NULL,
  206. `status` tinyint(255) NULL DEFAULT NULL,
  207. PRIMARY KEY (`column_id`) USING BTREE,
  208. INDEX `group_id`(`group_id`) USING BTREE
  209. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  210. -- ----------------------------
  211. -- Table structure for t_affair_system_column_group
  212. -- ----------------------------
  213. DROP TABLE IF EXISTS `t_affair_system_column_group`;
  214. CREATE TABLE `t_affair_system_column_group` (
  215. `group_id` int(11) NOT NULL AUTO_INCREMENT,
  216. `group_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  217. PRIMARY KEY (`group_id`) USING BTREE
  218. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  219. -- ----------------------------
  220. -- Table structure for t_bill
  221. -- ----------------------------
  222. DROP TABLE IF EXISTS `t_bill`;
  223. CREATE TABLE `t_bill` (
  224. `bill_id` int(11) NOT NULL AUTO_INCREMENT,
  225. `bill_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  226. `bill_number_user_defined` varchar(255) NULL,
  227. `bill_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  228. `bill_desc` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  229. `bill_count` int(11) NULL DEFAULT 0,
  230. `status` int(2) NULL DEFAULT 99,
  231. `ctime` datetime(0) NULL DEFAULT NULL,
  232. `utime` datetime(0) NULL DEFAULT NULL,
  233. `permission` int(1) NULL DEFAULT 1,
  234. `order_data` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  235. `bill_limit` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  236. `bill_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  237. `bill_affair_Ids` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  238. `bill_type` tinyint(1) NULL DEFAULT 0,
  239. `bill_template` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  240. `bill_merge_condition` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  241. `bill_set_explain` varchar(255) NULL DEFAULT '0',
  242. `bill_use_template` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  243. PRIMARY KEY (`bill_id`) USING BTREE
  244. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  245. -- ----------------------------
  246. -- Table structure for t_bill_order
  247. -- ----------------------------
  248. DROP TABLE IF EXISTS `t_bill_order`;
  249. CREATE TABLE `t_bill_order` (
  250. `bill_order_id` int(11) NOT NULL AUTO_INCREMENT,
  251. `bill_id` int(11) NULL DEFAULT NULL,
  252. `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  253. `bill_order_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  254. `bill_order_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  255. `bill_affair_data_Ids` varchar(550) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  256. `status` int(2) NULL DEFAULT 99,
  257. `ctime` datetime(0) NULL DEFAULT NULL,
  258. `ptime` datetime(0) NULL DEFAULT NULL,
  259. `bill_data` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  260. `printer_status` int(1) NULL DEFAULT 0,
  261. `bill_order_supplement` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  262. `bill_order_header` varchar(255) NULL,
  263. `bill_explain` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  264. `bill_object` varchar(13) NULL,
  265. `bill_amount` decimal(18, 3) NULL DEFAULT NULL,
  266. `bill_pnames` text NULL,
  267. `order_first_time` datetime NULL,
  268. `order_last_time` datetime NULL,
  269. PRIMARY KEY (`bill_order_id`) USING BTREE,
  270. INDEX `bill_id`(`bill_id`) USING BTREE,
  271. CONSTRAINT `t_bill_order_ibfk_1` FOREIGN KEY (`bill_id`) REFERENCES `t_bill` (`bill_id`) ON DELETE CASCADE ON UPDATE CASCADE
  272. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  273. -- ----------------------------
  274. -- Table structure for t_bill_permission
  275. -- ----------------------------
  276. DROP TABLE IF EXISTS `t_bill_permission`;
  277. CREATE TABLE `t_bill_permission` (
  278. `bill_per_id` int(11) NOT NULL AUTO_INCREMENT,
  279. `bill_id` int(11) NOT NULL,
  280. `user_id` int(11) NOT NULL DEFAULT 0,
  281. PRIMARY KEY (`bill_per_id`) USING BTREE,
  282. INDEX `user_id`(`user_id`) USING BTREE,
  283. INDEX `bill_per_id`(`bill_per_id`) USING BTREE,
  284. INDEX `bill_id`(`bill_id`) USING BTREE,
  285. CONSTRAINT `t_bill_permission_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  286. CONSTRAINT `t_bill_permission_ibfk_3` FOREIGN KEY (`bill_id`) REFERENCES `t_bill` (`bill_id`) ON DELETE CASCADE ON UPDATE CASCADE
  287. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  288. -- ----------------------------
  289. -- Table structure for t_bill_subtitle
  290. -- ----------------------------
  291. DROP TABLE IF EXISTS `t_bill_subtitle`;
  292. CREATE TABLE `t_bill_subtitle` (
  293. `bill_subtitle_id` int(11) NOT NULL AUTO_INCREMENT,
  294. `bill_id` int(11) NOT NULL,
  295. `user_ids` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  296. `bill_subtitle_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  297. PRIMARY KEY (`bill_subtitle_id`) USING BTREE,
  298. INDEX `bill_id`(`bill_id`) USING BTREE,
  299. CONSTRAINT `t_bill_subtitle_ibfk_2` FOREIGN KEY (`bill_id`) REFERENCES `t_bill` (`bill_id`) ON DELETE CASCADE ON UPDATE CASCADE
  300. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  301. -- ----------------------------
  302. -- Table structure for t_cas_st
  303. -- ----------------------------
  304. DROP TABLE IF EXISTS `t_cas_st`;
  305. CREATE TABLE `t_cas_st` (
  306. `st_id` varchar(23) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  307. `user_id` int(11) NULL DEFAULT NULL,
  308. `tgt_id` varchar(24) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  309. `st_service` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  310. `st_device` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  311. `deviceId` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  312. `deviceToken` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  313. `user_agent` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  314. `st_ip` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  315. `st_last_used` datetime(0) NULL DEFAULT NULL,
  316. `st_use_times` int(11) NULL DEFAULT NULL,
  317. `ctime` datetime(0) NULL DEFAULT NULL,
  318. `utime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  319. `dtime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  320. `status` int(11) NULL DEFAULT NULL,
  321. PRIMARY KEY (`st_id`) USING BTREE,
  322. INDEX `tgt_id`(`tgt_id`) USING BTREE,
  323. CONSTRAINT `t_cas_st_ibfk_1` FOREIGN KEY (`tgt_id`) REFERENCES `t_cas_tgt` (`tgt_id`) ON DELETE CASCADE ON UPDATE CASCADE
  324. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  325. -- ----------------------------
  326. -- Table structure for t_cas_tgt
  327. -- ----------------------------
  328. DROP TABLE IF EXISTS `t_cas_tgt`;
  329. CREATE TABLE `t_cas_tgt` (
  330. `tgt_id` varchar(24) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  331. `user_id` int(11) NULL DEFAULT NULL,
  332. `tgt_last_used` datetime(0) NULL DEFAULT NULL,
  333. `tgt_use_times` int(11) NULL DEFAULT NULL,
  334. `tgt_platform` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  335. `tgt_kick_step` int(255) NULL DEFAULT NULL,
  336. `ctime` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  337. PRIMARY KEY (`tgt_id`) USING BTREE
  338. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  339. -- ----------------------------
  340. -- Table structure for t_collection
  341. -- ----------------------------
  342. DROP TABLE IF EXISTS `t_collection`;
  343. CREATE TABLE `t_collection` (
  344. `collection_id` int(11) NOT NULL AUTO_INCREMENT,
  345. `user_id` int(11) NULL DEFAULT NULL,
  346. `company_id` int(11) NOT NULL,
  347. `image_id` int(11) NULL DEFAULT NULL,
  348. `product_id` int(11) NULL DEFAULT NULL,
  349. `collection_image_id` int(11) NULL DEFAULT NULL,
  350. `collection_image` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  351. `collection_pname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  352. `collection_cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  353. `collection_type` tinyint(2) NOT NULL DEFAULT 0,
  354. `collection_state` tinyint(2) NULL DEFAULT 0,
  355. `collection_price` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  356. `collection_price_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  357. `collection_inventory` decimal(13, 3) NULL DEFAULT NULL,
  358. `collection_inventory_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  359. `collection_width` decimal(13, 2) NULL DEFAULT NULL,
  360. `collection_width_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  361. `collection_height` decimal(13, 2) NULL DEFAULT NULL,
  362. `collection_height_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  363. `collection_freeheight` decimal(13, 2) NULL DEFAULT NULL,
  364. `collection_freeheight_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  365. `collection_weight` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  366. `collection_weight_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  367. `collection_elasticity` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  368. `collection_mtype` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  369. `collection_material` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  370. `collection_notes` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  371. `collection_gram_weight` decimal(13,2) NULL DEFAULT NULL,
  372. `collection_gram_weight_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT 'g/m2',
  373. `collection_tag_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  374. `product_cloth_price_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  375. `product_product_price_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  376. `product_cloth_price` decimal(13, 2) NULL DEFAULT NULL,
  377. `product_product_price` decimal(13, 2) NULL DEFAULT NULL,
  378. `push_result` longtext NULL,
  379. `recv_result` longtext NULL,
  380. `search_id` int(11) NULL,
  381. `search_status` int(11) NULL,
  382. `ctime` datetime(0) NOT NULL,
  383. `utime` datetime(0) NULL DEFAULT NULL,
  384. `dtime` datetime(0) NULL DEFAULT NULL,
  385. `status` tinyint(2) NOT NULL DEFAULT 99,
  386. PRIMARY KEY (`collection_id`) USING BTREE,
  387. INDEX `image_id`(`image_id`) USING BTREE,
  388. INDEX `user_id`(`user_id`) USING BTREE,
  389. INDEX `company_id`(`company_id`) USING BTREE,
  390. FULLTEXT INDEX `collection_pname`(`collection_pname`),
  391. FULLTEXT INDEX `collection_cname`(`collection_cname`),
  392. CONSTRAINT `t_collection_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  393. CONSTRAINT `t_collection_ibfk_3` FOREIGN KEY (`company_id`) REFERENCES `t_company` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE
  394. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  395. -- ----------------------------
  396. -- Table structure for t_company
  397. -- ----------------------------
  398. DROP TABLE IF EXISTS `t_company`;
  399. CREATE TABLE `t_company` (
  400. `company_id` int(11) NOT NULL AUTO_INCREMENT,
  401. `location_id` int(11) NULL DEFAULT NULL,
  402. `company_type` tinyint(1) NULL DEFAULT 0,
  403. `company_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  404. `company_shortname` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  405. `company_major` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  406. `company_urlname` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  407. `company_cert` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  408. `company_tel` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  409. `company_fax` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  410. `company_qq` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  411. `company_email` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  412. `company_ctime` date NULL DEFAULT NULL,
  413. `company_addr` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  414. `company_corporation` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  415. `company_copy` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  416. `company_notes` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  417. `viewtimes` int(11) NULL DEFAULT 0,
  418. `company_follows` int(255) NULL DEFAULT 0,
  419. `is_vip` tinyint(1) NULL DEFAULT 0,
  420. `status` int(255) NULL DEFAULT 0,
  421. `ctime` datetime(0) NOT NULL,
  422. `utime` datetime(0) NULL DEFAULT NULL,
  423. `dtime` datetime(0) NULL DEFAULT NULL,
  424. `tagIds` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  425. `customerGroupId` int(11) NULL DEFAULT 0,
  426. `company_program` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  427. `version` int(11) NULL DEFAULT NULL,
  428. `company_phone` varchar(255) NULL,
  429. PRIMARY KEY (`company_id`) USING BTREE,
  430. INDEX `FK_Reference_29`(`location_id`) USING BTREE,
  431. INDEX `customerGroupId`(`customerGroupId`) USING BTREE,
  432. INDEX `company_name`(`company_name`) USING BTREE,
  433. INDEX `company_shortname`(`company_shortname`) USING BTREE,
  434. INDEX `company_urlname`(`company_urlname`) USING BTREE,
  435. CONSTRAINT `FK_Reference_29` FOREIGN KEY (`location_id`) REFERENCES `t_location` (`location_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  436. CONSTRAINT `t_company_ibfk_1` FOREIGN KEY (`customerGroupId`) REFERENCES `t_customer_group` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
  437. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  438. -- ----------------------------
  439. -- Table structure for t_customer
  440. -- ----------------------------
  441. DROP TABLE IF EXISTS `t_customer`;
  442. CREATE TABLE `t_customer` (
  443. `id` int(11) NOT NULL AUTO_INCREMENT,
  444. `companyId` int(11) NOT NULL DEFAULT 0,
  445. `addUserId` int(11) NOT NULL,
  446. `customerCompanyId` int(11) NULL DEFAULT NULL,
  447. `avatarImageId` int(11) NULL DEFAULT NULL,
  448. `avatarImage` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  449. `customer_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  450. `customer_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  451. `customer_qq` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  452. `customer_email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  453. `customer_notes` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  454. `tagIds` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  455. `visitUserIds` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  456. `state` int(11) NULL DEFAULT 0,
  457. `status` int(11) NULL DEFAULT 99,
  458. `createAt` datetime(0) NULL DEFAULT NULL,
  459. `updateAt` datetime(0) NULL DEFAULT NULL,
  460. `deleteAt` datetime(0) NULL DEFAULT NULL,
  461. `groupId` int(11) NULL DEFAULT NULL,
  462. `card1ImageId` int(11) NULL DEFAULT NULL,
  463. `card2ImageId` int(11) NULL DEFAULT NULL,
  464. `card3ImageId` int(11) NULL DEFAULT NULL,
  465. `representative` tinyint(1) NULL DEFAULT 0,
  466. `customer_add` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  467. `customer_phone2` varchar(255) NULL DEFAULT '',
  468. `customer_phone3` varchar(255) NULL DEFAULT '',
  469. PRIMARY KEY (`id`) USING BTREE,
  470. INDEX `companyId`(`companyId`) USING BTREE,
  471. INDEX `t_customer_ibfk_2`(`customerCompanyId`) USING BTREE,
  472. INDEX `id`(`id`, `customerCompanyId`) USING BTREE,
  473. INDEX `groupId`(`groupId`) USING BTREE,
  474. CONSTRAINT `t_customer_ibfk_1` FOREIGN KEY (`companyId`) REFERENCES `t_company` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  475. CONSTRAINT `t_customer_ibfk_2` FOREIGN KEY (`customerCompanyId`) REFERENCES `t_company` (`company_id`) ON DELETE SET NULL ON UPDATE RESTRICT,
  476. CONSTRAINT `t_customer_ibfk_3` FOREIGN KEY (`groupId`) REFERENCES `t_customer_group` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
  477. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
  478. -- ----------------------------
  479. -- Table structure for t_customer_group
  480. -- ----------------------------
  481. DROP TABLE IF EXISTS `t_customer_group`;
  482. CREATE TABLE `t_customer_group` (
  483. `id` int(11) NOT NULL AUTO_INCREMENT,
  484. `companyId` int(11) NOT NULL,
  485. `userId` int(11) NOT NULL,
  486. `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  487. `notes` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  488. `usedCount` int(11) NULL DEFAULT 0,
  489. `status` int(11) NULL DEFAULT 99,
  490. `createAt` datetime(0) NULL DEFAULT NULL,
  491. `updateAt` datetime(0) NULL DEFAULT NULL,
  492. `deleteAt` datetime(0) NULL DEFAULT NULL,
  493. `permission` tinyint(1) NULL DEFAULT 1,
  494. `user_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL,
  495. PRIMARY KEY (`id`) USING BTREE,
  496. INDEX `companyId`(`companyId`) USING BTREE,
  497. INDEX `userId`(`userId`) USING BTREE,
  498. CONSTRAINT `t_customer_group_ibfk_1` FOREIGN KEY (`companyId`) REFERENCES `t_company` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  499. CONSTRAINT `t_customer_group_ibfk_2` FOREIGN KEY (`userId`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
  500. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  501. -- ----------------------------
  502. -- Table structure for t_department
  503. -- ----------------------------
  504. DROP TABLE IF EXISTS `t_department`;
  505. CREATE TABLE `t_department` (
  506. `department_id` int(255) NOT NULL AUTO_INCREMENT,
  507. `department_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  508. `department_pid` int(255) NULL DEFAULT 0,
  509. PRIMARY KEY (`department_id`) USING BTREE
  510. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  511. -- ----------------------------
  512. -- Table structure for t_documentary
  513. -- ----------------------------
  514. DROP TABLE IF EXISTS `t_documentary`;
  515. CREATE TABLE `t_documentary` (
  516. `documentary_id` int(11) NOT NULL AUTO_INCREMENT,
  517. `documentary_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  518. `documentary_number_pid` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  519. `documentary_product_id` int(11) NULL DEFAULT NULL,
  520. `documentary_object` varchar(13) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  521. `documentary_summary` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  522. `documentary_state` tinyint(1) NULL DEFAULT 0,
  523. `data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  524. `companyId` int(11) NULL DEFAULT NULL,
  525. `user_id` int(11) NULL DEFAULT NULL,
  526. `ctime` datetime(0) NULL DEFAULT NULL,
  527. `utime` datetime(0) NULL DEFAULT NULL,
  528. `status` tinyint(2) NULL DEFAULT 99,
  529. PRIMARY KEY (`documentary_id`) USING BTREE,
  530. UNIQUE INDEX `documentary_number` (`documentary_number_pid`, `documentary_number`) USING BTREE
  531. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  532. -- ----------------------------
  533. -- Table structure for t_documentary_report
  534. -- ----------------------------
  535. DROP TABLE IF EXISTS `t_documentary_report`;
  536. CREATE TABLE `t_documentary_report` (
  537. `report_id` int(11) NOT NULL AUTO_INCREMENT,
  538. `documentary_data_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  539. `reply_user_id` int(11) NULL DEFAULT NULL,
  540. `reply_txt` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  541. `reply_image` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  542. `reply_voice` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  543. `reply_time` datetime(0) NULL DEFAULT NULL,
  544. PRIMARY KEY (`report_id`) USING BTREE
  545. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  546. -- ----------------------------
  547. -- Table structure for t_file_list
  548. -- ----------------------------
  549. DROP TABLE IF EXISTS `t_file_list`;
  550. CREATE TABLE `t_file_list` (
  551. `id` int(11) NOT NULL AUTO_INCREMENT,
  552. `pid` int(11) NULL DEFAULT NULL,
  553. `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  554. `md5` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  555. `crop` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  556. `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  557. `category` int(255) NULL DEFAULT NULL,
  558. `props` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  559. `mime` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  560. `extension` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  561. `size` int(11) NULL DEFAULT NULL,
  562. `type` int(255) NULL DEFAULT NULL,
  563. `icat` int(255) NULL DEFAULT NULL,
  564. `icatmp` int(255) NULL DEFAULT NULL,
  565. `visitimes` int(11) NULL DEFAULT NULL,
  566. `ctime` datetime(0) NULL DEFAULT NULL,
  567. `utime` datetime(0) NULL DEFAULT NULL,
  568. `dtime` datetime(0) NULL DEFAULT NULL,
  569. `status` int(255) NULL DEFAULT NULL,
  570. `user_id` int(11) NULL DEFAULT NULL,
  571. `template_number` varchar(50) NULL DEFAULT '',
  572. PRIMARY KEY (`id`) USING BTREE,
  573. UNIQUE INDEX `md5`(`md5`, `type`, `category`) USING BTREE
  574. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  575. -- ----------------------------
  576. -- Table structure for t_group_position
  577. -- ----------------------------
  578. DROP TABLE IF EXISTS `t_group_position`;
  579. CREATE TABLE `t_group_position` (
  580. `position_id` int(11) NOT NULL AUTO_INCREMENT,
  581. `user_id` int(11) NOT NULL,
  582. `groupIds` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  583. `utime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  584. `position_type` tinyint(1) NULL DEFAULT 0,
  585. PRIMARY KEY (`position_id`) USING BTREE,
  586. INDEX `user_id`(`user_id`) USING BTREE,
  587. CONSTRAINT `t_group_position_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
  588. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;
  589. -- ----------------------------
  590. -- Table structure for t_inventory
  591. -- ----------------------------
  592. DROP TABLE IF EXISTS `t_inventory`;
  593. CREATE TABLE `t_inventory` (
  594. `inventory_id` int(11) NOT NULL AUTO_INCREMENT,
  595. `warehouse_product_id` int(11) NULL DEFAULT NULL,
  596. `warehouse_product_color` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  597. `warehouse_product_color_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  598. `warehouse_product_height` double(22, 2) NULL DEFAULT NULL,
  599. `warehouse_product_width` double(22, 2) NULL DEFAULT NULL,
  600. `warehouse_material_composition` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  601. `warehouse_machine_id` int(11) NULL DEFAULT NULL,
  602. `warehouse_storage_area` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  603. `inventory_quantity_unit` int(1) NULL DEFAULT 0,
  604. `warehouse_other_description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  605. `status` int(2) NULL DEFAULT 99,
  606. `ctime` datetime(0) NULL DEFAULT NULL,
  607. `utime` datetime(0) NULL DEFAULT NULL,
  608. `inventory_price_unit` tinyint(1) NULL,
  609. `warehouse_collection_id` int(11) NULL DEFAULT NULL,
  610. PRIMARY KEY (`inventory_id`) USING BTREE,
  611. INDEX `warehouse_product_id`(`warehouse_product_id`) USING BTREE,
  612. INDEX `warehouse_machine_id`(`warehouse_machine_id`) USING BTREE,
  613. CONSTRAINT `t_inventory_ibfk_1` FOREIGN KEY (`warehouse_product_id`) REFERENCES `t_product` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  614. CONSTRAINT `t_inventory_ibfk_2` FOREIGN KEY (`warehouse_machine_id`) REFERENCES `t_machine` (`machine_id`) ON DELETE CASCADE ON UPDATE CASCADE
  615. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  616. -- ----------------------------
  617. -- Table structure for t_inventory_calculation
  618. -- ----------------------------
  619. DROP TABLE IF EXISTS `t_inventory_calculation`;
  620. CREATE TABLE `t_inventory_calculation` (
  621. `calculation_id` int(11) NOT NULL AUTO_INCREMENT,
  622. `data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  623. `affair_id` int(11) NOT NULL,
  624. `tagIds` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  625. `calculation_key` int(11) NOT NULL,
  626. `calculation_time` datetime(0) NOT NULL,
  627. `calculation_value` decimal(22, 6) NOT NULL,
  628. `calculation_value2` decimal(22, 6) NOT NULL,
  629. `calculation_condition` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  630. `calculation_note` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  631. `call_time` datetime(0) NOT NULL,
  632. `from_affair_id` int(11) NOT NULL,
  633. `self_built_source` varchar(255) NOT NULL,
  634. `tag_numbers` tinyint(1) NULL DEFAULT 0,
  635. `from_data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  636. `offer_price` decimal(22,3) NULL DEFAULT -1,
  637. `offer_product_total_cost` decimal(22,6) NULL,
  638. `calculation_ctime` datetime NULL DEFAULT NULL,
  639. PRIMARY KEY (`calculation_id`, `data_id`) USING BTREE,
  640. INDEX `call_time`(`call_time`) USING BTREE,
  641. INDEX `calculation_value`(`calculation_key`, `calculation_time`, `calculation_value`, `calculation_value2`, `calculation_condition`, `tagIds`) USING BTREE
  642. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  643. -- ----------------------------
  644. -- Table structure for t_inventory_record
  645. -- ----------------------------
  646. DROP TABLE IF EXISTS `t_inventory_record`;
  647. CREATE TABLE `t_inventory_record` (
  648. `inventory_record_id` int(11) NOT NULL AUTO_INCREMENT,
  649. `inventory_id` int(11) NOT NULL,
  650. `record_time` datetime(0) NOT NULL,
  651. `inventory_quantity` double(22, 2) NOT NULL,
  652. `inventory_quantity_reserve` double(22, 2) NULL DEFAULT 0.00,
  653. `notes` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  654. `status` int(2) NULL DEFAULT 99,
  655. `ctime` datetime(0) NULL DEFAULT NULL,
  656. `utime` datetime(0) NULL DEFAULT NULL,
  657. `user_id` int(11) NULL,
  658. PRIMARY KEY (`inventory_record_id`) USING BTREE,
  659. INDEX `inventory_id`(`inventory_id`) USING BTREE,
  660. CONSTRAINT `t_inventory_record_ibfk_1` FOREIGN KEY (`inventory_id`) REFERENCES `t_inventory` (`inventory_id`) ON DELETE CASCADE ON UPDATE CASCADE
  661. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  662. -- ----------------------------
  663. -- Table structure for t_keyword_log
  664. -- ----------------------------
  665. DROP TABLE IF EXISTS `t_keyword_log`;
  666. CREATE TABLE `t_keyword_log` (
  667. `keyword_id` int(11) NOT NULL AUTO_INCREMENT,
  668. `user_id` int(11) NULL DEFAULT NULL,
  669. `company_id` int(11) NULL DEFAULT NULL,
  670. `keyword_category` int(11) NULL DEFAULT NULL,
  671. `keyword_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  672. `keyword_values` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  673. `status` int(11) NULL DEFAULT 99,
  674. `ctime` datetime(0) NULL DEFAULT NULL,
  675. `dtime` datetime(0) NULL DEFAULT NULL,
  676. PRIMARY KEY (`keyword_id`) USING BTREE,
  677. INDEX `user_id`(`user_id`) USING BTREE,
  678. INDEX `company_id`(`company_id`) USING BTREE,
  679. CONSTRAINT `t_keyword_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  680. CONSTRAINT `t_keyword_log_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `t_company` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE
  681. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  682. -- ----------------------------
  683. -- Table structure for t_location
  684. -- ----------------------------
  685. DROP TABLE IF EXISTS `t_location`;
  686. CREATE TABLE `t_location` (
  687. `location_id` int(11) NOT NULL AUTO_INCREMENT,
  688. `location_pid` int(11) NULL DEFAULT NULL,
  689. `location_name` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  690. `location_postnum` varchar(6) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  691. `location_level` tinyint(1) NULL DEFAULT NULL,
  692. `location_phonenum` varchar(5) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  693. PRIMARY KEY (`location_id`) USING BTREE,
  694. INDEX `FK_Reference_28`(`location_pid`) USING BTREE,
  695. CONSTRAINT `FK_Reference_28` FOREIGN KEY (`location_pid`) REFERENCES `t_location` (`location_id`) ON DELETE SET NULL ON UPDATE CASCADE
  696. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  697. -- ----------------------------
  698. -- Table structure for t_log_edit
  699. -- ----------------------------
  700. DROP TABLE IF EXISTS `t_log_edit`;
  701. CREATE TABLE `t_log_edit` (
  702. `log_edit_id` int(11) NOT NULL AUTO_INCREMENT,
  703. `log_company_id` int(11) NULL DEFAULT NULL,
  704. `log_user_id` int(11) NULL DEFAULT NULL,
  705. `log_edit_clazz` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  706. `log_edit_oldvalue` longtext CHARACTER SET utf8 COLLATE utf8_bin NULL,
  707. `log_edit_newvalue` longtext CHARACTER SET utf8 COLLATE utf8_bin NULL,
  708. `log_action` tinyint(1) NULL DEFAULT NULL,
  709. `ctime` datetime(0) NULL DEFAULT NULL,
  710. PRIMARY KEY (`log_edit_id`) USING BTREE
  711. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  712. -- ----------------------------
  713. -- Table structure for t_machine
  714. -- ----------------------------
  715. DROP TABLE IF EXISTS `t_machine`;
  716. CREATE TABLE `t_machine` (
  717. `machine_id` int(11) NOT NULL AUTO_INCREMENT,
  718. `machine_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  719. `machine_horse` int(11) NULL DEFAULT 0,
  720. `machine_des` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  721. `machine_state` int(2) NOT NULL DEFAULT 0,
  722. `affair_data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  723. `pause_affair_data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  724. `model_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  725. `status` int(2) NOT NULL DEFAULT 99,
  726. `ctime` datetime(0) NOT NULL,
  727. `utime` datetime(0) NULL DEFAULT NULL,
  728. `dtime` datetime(0) NULL DEFAULT NULL,
  729. PRIMARY KEY (`machine_id`) USING BTREE,
  730. INDEX `machine_id`(`machine_id`) USING BTREE,
  731. INDEX `model_name`(`model_name`) USING BTREE,
  732. CONSTRAINT `t_machine_ibfk_1` FOREIGN KEY (`model_name`) REFERENCES `t_machine_model` (`model_name`) ON DELETE CASCADE ON UPDATE CASCADE
  733. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  734. -- ----------------------------
  735. -- Table structure for t_machine_model
  736. -- ----------------------------
  737. DROP TABLE IF EXISTS `t_machine_model`;
  738. CREATE TABLE `t_machine_model` (
  739. `model_id` int(11) NOT NULL AUTO_INCREMENT,
  740. `model_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  741. `model_des` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  742. `status` int(11) NULL DEFAULT 99,
  743. `ctime` datetime(0) NOT NULL,
  744. `utime` datetime(0) NULL DEFAULT NULL,
  745. PRIMARY KEY (`model_id`) USING BTREE,
  746. INDEX `model_name`(`model_name`) USING BTREE
  747. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  748. -- ----------------------------
  749. -- Table structure for t_machine_record
  750. -- ----------------------------
  751. DROP TABLE IF EXISTS `t_machine_record`;
  752. CREATE TABLE `t_machine_record` (
  753. `record_id` int(11) NOT NULL AUTO_INCREMENT,
  754. `machine_id` int(11) NOT NULL,
  755. `record_des` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  756. `affair_data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  757. `change_time` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  758. `production_time` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  759. `stop_time` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  760. `utime` datetime(0) NOT NULL,
  761. `ctime` datetime(0) NOT NULL,
  762. PRIMARY KEY (`record_id`) USING BTREE
  763. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  764. -- ----------------------------
  765. -- Table structure for t_machine_settings
  766. -- ----------------------------
  767. DROP TABLE IF EXISTS `t_machine_settings`;
  768. CREATE TABLE `t_machine_settings` (
  769. `id` int(11) NOT NULL AUTO_INCREMENT,
  770. `produced_affair_id` int(11) NULL DEFAULT NULL,
  771. `produced_sheet_id` int(11) NULL DEFAULT NULL,
  772. `produced_affair_tag_ids` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  773. `produced_user_ids` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  774. `produced_affair_summary` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  775. `produced_affair_summary_permission` tinyint(1) NULL DEFAULT 1,
  776. `utime` datetime(0) NULL DEFAULT NULL,
  777. PRIMARY KEY (`id`) USING BTREE,
  778. INDEX `produced_sheet`(`produced_sheet_id`) USING BTREE,
  779. INDEX `produced_affair_id`(`produced_affair_id`) USING BTREE,
  780. CONSTRAINT `t_machine_settings_ibfk_3` FOREIGN KEY (`produced_sheet_id`) REFERENCES `t_affair_sheet` (`sheet_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  781. CONSTRAINT `t_machine_settings_ibfk_4` FOREIGN KEY (`produced_affair_id`) REFERENCES `t_affair` (`affair_id`) ON DELETE CASCADE ON UPDATE CASCADE
  782. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  783. -- ----------------------------
  784. -- Table structure for t_pattribute
  785. -- ----------------------------
  786. DROP TABLE IF EXISTS `t_pattribute`;
  787. CREATE TABLE `t_pattribute` (
  788. `pattribute_id` int(11) NOT NULL AUTO_INCREMENT,
  789. `user_id` int(11) NULL DEFAULT NULL,
  790. `company_id` int(11) NULL DEFAULT NULL,
  791. `product_id` int(11) NOT NULL,
  792. `image_id` int(11) NULL DEFAULT NULL,
  793. `pattribute_image_id` int(11) NULL DEFAULT NULL,
  794. `pattribute_image` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  795. `pattribute_pname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  796. `pattribute_cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  797. `pattribute_type` int(255) NULL DEFAULT NULL,
  798. `pattribute_price` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  799. `pattribute_price_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  800. `pattribute_inventory` decimal(13, 3) NULL DEFAULT NULL,
  801. `pattribute_inventory_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  802. `pattribute_width` decimal(13, 2) NULL DEFAULT NULL,
  803. `pattribute_width_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  804. `pattribute_height` decimal(13, 2) NULL DEFAULT NULL,
  805. `pattribute_height_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  806. `pattribute_freeheight` decimal(13, 2) NULL DEFAULT NULL,
  807. `pattribute_freeheight_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  808. `pattribute_weight` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  809. `pattribute_weight_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  810. `pattribute_elasticity` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  811. `pattribute_mtype` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  812. `pattribute_material` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  813. `pattribute_notes` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  814. `pattribute_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  815. `pattribute_from` tinyint(1) NULL DEFAULT NULL,
  816. `ctime` datetime(0) NULL DEFAULT NULL,
  817. PRIMARY KEY (`pattribute_id`) USING BTREE,
  818. UNIQUE INDEX `pattribute_id`(`pattribute_id`) USING BTREE,
  819. INDEX `product_id`(`product_id`) USING BTREE,
  820. INDEX `image_id`(`image_id`) USING BTREE,
  821. CONSTRAINT `t_pattribute_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `t_product` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE
  822. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  823. -- ----------------------------
  824. -- Table structure for t_pattribute_linkman
  825. -- ----------------------------
  826. DROP TABLE IF EXISTS `t_pattribute_linkman`;
  827. CREATE TABLE `t_pattribute_linkman` (
  828. `linkman_id` int(11) NOT NULL AUTO_INCREMENT,
  829. `user_id` int(11) NULL DEFAULT NULL,
  830. `pattribute_id` int(11) NULL DEFAULT NULL,
  831. `linkman_realname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  832. `linkman_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  833. `linkman_qq` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  834. `linkman_email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  835. `linkman_rolename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  836. `ctime` datetime(0) NULL DEFAULT NULL,
  837. PRIMARY KEY (`linkman_id`) USING BTREE,
  838. UNIQUE INDEX `linkman_id`(`linkman_id`) USING BTREE,
  839. INDEX `pattribute_id`(`pattribute_id`) USING BTREE,
  840. INDEX `user_id`(`user_id`) USING BTREE,
  841. CONSTRAINT `t_pattribute_linkman_ibfk_1` FOREIGN KEY (`pattribute_id`) REFERENCES `t_pattribute` (`pattribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  842. CONSTRAINT `t_pattribute_linkman_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE
  843. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  844. -- ----------------------------
  845. -- Table structure for t_printer
  846. -- ----------------------------
  847. DROP TABLE IF EXISTS `t_printer`;
  848. CREATE TABLE `t_printer` (
  849. `printer_id` int(11) NOT NULL AUTO_INCREMENT,
  850. `need_user_id` int(11) NULL DEFAULT NULL,
  851. `printer_user_id` int(11) NULL DEFAULT NULL,
  852. `printer_type` int(11) NULL DEFAULT NULL,
  853. `printer_from_id` int(11) NULL DEFAULT NULL,
  854. `printer_data` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  855. `printer_state` int(11) NULL DEFAULT 0,
  856. `ctime` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  857. `ptime` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  858. `ttime` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  859. `printer_page` int(11) NULL,
  860. `version` int(11) NULL,
  861. PRIMARY KEY (`printer_id`) USING BTREE,
  862. UNIQUE INDEX `printer_id`(`printer_id`) USING BTREE,
  863. INDEX `need_user_id`(`need_user_id`) USING BTREE,
  864. INDEX `printer_user_id`(`printer_user_id`) USING BTREE,
  865. CONSTRAINT `t_printer_ibfk_1` FOREIGN KEY (`need_user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  866. CONSTRAINT `t_printer_ibfk_2` FOREIGN KEY (`printer_user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
  867. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  868. -- ----------------------------
  869. -- Table structure for t_printer_frequently
  870. -- ----------------------------
  871. DROP TABLE IF EXISTS `t_printer_frequently`;
  872. CREATE TABLE `t_printer_frequently` (
  873. `frequently_id` int(11) NOT NULL AUTO_INCREMENT,
  874. `user_id` int(11) NULL DEFAULT NULL,
  875. `printer_user_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  876. `utime` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  877. UNIQUE INDEX `frequently_id`(`frequently_id`) USING BTREE,
  878. INDEX `user_id`(`user_id`) USING BTREE,
  879. CONSTRAINT `t_printer_frequently_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
  880. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;
  881. -- ----------------------------
  882. -- Table structure for t_product
  883. -- ----------------------------
  884. DROP TABLE IF EXISTS `t_product`;
  885. CREATE TABLE `t_product` (
  886. `product_id` int(11) NOT NULL AUTO_INCREMENT,
  887. `user_id` int(11) NULL DEFAULT NULL,
  888. `company_id` int(11) NOT NULL,
  889. `company_type` int(11) NULL DEFAULT NULL,
  890. `image_id` int(11) NULL DEFAULT NULL,
  891. `product_image_id` int(11) NULL DEFAULT NULL,
  892. `product_image` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  893. `product_pname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  894. `product_cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  895. `product_type` tinyint(2) NULL DEFAULT NULL,
  896. `product_state` tinyint(2) NULL DEFAULT 0,
  897. `product_price` varchar(13) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  898. `product_price_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  899. `product_cloth_price` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  900. `product_cloth_price_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  901. `product_product_price` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  902. `product_product_price_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  903. `product_inventory` decimal(13, 3) NULL DEFAULT NULL,
  904. `product_inventory_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  905. `product_width` decimal(13, 2) NULL DEFAULT NULL,
  906. `product_width_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  907. `product_height` decimal(13, 2) NULL DEFAULT NULL,
  908. `product_height_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  909. `product_freeheight` decimal(13, 2) NULL DEFAULT NULL,
  910. `product_freeheight_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  911. `product_weight` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  912. `product_weight_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  913. `product_elasticity` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  914. `product_mtype` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  915. `product_material` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  916. `product_uptime` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  917. `product_uptime_density` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  918. `product_uptime_density_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  919. `product_notes` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  920. `product_chart` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  921. `chart_image_id` int(11) NULL DEFAULT NULL,
  922. `chart_image` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  923. `product_hits` int(255) NULL DEFAULT 0,
  924. `product_tag_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  925. `ctime` datetime(0) NOT NULL,
  926. `utime` datetime(0) NULL DEFAULT NULL,
  927. `dtime` datetime(0) NULL DEFAULT NULL,
  928. `status` tinyint(2) NOT NULL DEFAULT 99,
  929. `product_exhibition_id` int(11) NULL DEFAULT NULL,
  930. `product_partially_id` int(11) NULL DEFAULT NULL,
  931. `product_beginning_id` int(11) NULL DEFAULT NULL,
  932. `product_gram_weight` decimal(13, 2) NULL DEFAULT NULL,
  933. `product_gram_weight_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT 'g/m2',
  934. `push_result` longtext NULL,
  935. `recv_result` longtext NULL,
  936. `product_public_notes` longtext CHARACTER SET utf8 COLLATE utf8_bin NULL,
  937. `search_id` int(11) NULL,
  938. `search_status` int(11) NULL,
  939. `product_cabinet_id` int(11) NULL,
  940. `product_partially` text CHARACTER SET utf8mb4 NULL,
  941. PRIMARY KEY (`product_id`) USING BTREE,
  942. UNIQUE INDEX `product_id`(`product_id`) USING BTREE,
  943. INDEX `FK_Relationship_12`(`user_id`) USING BTREE,
  944. INDEX `ctime`(`ctime`) USING BTREE,
  945. INDEX `status`(`status`) USING BTREE,
  946. INDEX `company_id`(`company_id`) USING BTREE,
  947. INDEX `image_id`(`image_id`) USING BTREE,
  948. INDEX `product_pname`(`product_pname`, `product_cname`, `product_type`, `status`) USING BTREE,
  949. CONSTRAINT `t_product_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `t_company` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  950. CONSTRAINT `t_product_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  951. CONSTRAINT `t_product_ibfk_4` FOREIGN KEY (`product_cabinet_id`) REFERENCES `t_confidential_cabinet` (`cabinet_id`) ON DELETE SET NULL ON UPDATE CASCADE
  952. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  953. -- ----------------------------
  954. -- Table structure for t_product_share
  955. -- ----------------------------
  956. DROP TABLE IF EXISTS `t_product_share`;
  957. CREATE TABLE `t_product_share` (
  958. `share_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  959. `product_ids` longtext CHARACTER SET utf8 COLLATE utf8_bin NULL,
  960. `user_id` int(11) NULL DEFAULT NULL,
  961. `company_id` int(11) NULL DEFAULT NULL,
  962. `title` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  963. `ctime` datetime NULL,
  964. `share_time` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  965. `desc` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  966. `imgUrl` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  967. `share_type` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  968. `share_password` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  969. `hits` int(11) NULL DEFAULT 0,
  970. PRIMARY KEY (`share_id`) USING BTREE
  971. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;
  972. -- ----------------------------
  973. -- Table structure for t_product_template
  974. -- ----------------------------
  975. DROP TABLE IF EXISTS `t_product_template`;
  976. CREATE TABLE `t_product_template` (
  977. `template_md5` varchar(50) COLLATE utf8_bin NOT NULL,
  978. `user_id` int(11) DEFAULT NULL,
  979. `company_id` int(11) DEFAULT NULL,
  980. `ctime` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  981. `template_imgUrl` varchar(255) COLLATE utf8_bin DEFAULT '',
  982. `template_number` varchar(25) COLLATE utf8_bin DEFAULT '',
  983. `template_color` varchar(50) COLLATE utf8_bin DEFAULT '',
  984. PRIMARY KEY (`template_md5`)
  985. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT;
  986. -- ----------------------------
  987. -- Table structure for t_r_product_collection
  988. -- ----------------------------
  989. DROP TABLE IF EXISTS `t_r_product_collection`;
  990. CREATE TABLE `t_r_product_collection` (
  991. `collection_id` int(11) NOT NULL,
  992. `product_id` int(11) NOT NULL,
  993. PRIMARY KEY (`collection_id`, `product_id`) USING BTREE,
  994. INDEX `product_id`(`product_id`) USING BTREE,
  995. INDEX `collection_id_2`(`collection_id`) USING BTREE,
  996. CONSTRAINT `t_r_product_collection_ibfk_1` FOREIGN KEY (`collection_id`) REFERENCES `t_collection` (`collection_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  997. CONSTRAINT `t_r_product_collection_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `t_product` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE
  998. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  999. -- ----------------------------
  1000. -- Table structure for t_report_forms
  1001. -- ----------------------------
  1002. DROP TABLE IF EXISTS `t_report_forms`;
  1003. CREATE TABLE `t_report_forms` (
  1004. `report_forms_id` int(11) NOT NULL AUTO_INCREMENT,
  1005. `report_forms_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1006. `report_forms_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1007. `report_forms_desc` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1008. `report_forms_count` int(11) NULL DEFAULT 0,
  1009. `status` int(2) NULL DEFAULT 99,
  1010. `ctime` datetime(0) NULL DEFAULT NULL,
  1011. `utime` datetime(0) NULL DEFAULT NULL,
  1012. `forms_data` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1013. `report_forms_limit` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1014. `report_forms_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1015. `forms_affair_Ids` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1016. `tag_limit` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1017. `set_begin` tinyint(1) NULL DEFAULT 2,
  1018. `set_inventory` tinyint(1) NULL DEFAULT 2,
  1019. `report_forms_pid` int(11) NULL DEFAULT NULL,
  1020. `report_forms_prompt` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  1021. `print_forms_data` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1022. `report_forms_group_id` int(11) NULL DEFAULT NULL,
  1023. `set_seal_account` tinyint(1) NULL DEFAULT 2,
  1024. `set_achievements` tinyint(1) NULL DEFAULT 2,
  1025. `set_company_account` tinyint(1) NULL DEFAULT 2,
  1026. PRIMARY KEY (`report_forms_id`) USING BTREE,
  1027. INDEX `report_forms_group_id`(`report_forms_group_id`) USING BTREE,
  1028. CONSTRAINT `t_report_forms_ibfk_1` FOREIGN KEY (`report_forms_group_id`) REFERENCES `t_report_forms_group` (`report_forms_group_id`) ON DELETE SET NULL ON UPDATE NO ACTION
  1029. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  1030. -- ----------------------------
  1031. -- Table structure for t_report_forms_begin
  1032. -- ----------------------------
  1033. DROP TABLE IF EXISTS `t_report_forms_begin`;
  1034. CREATE TABLE `t_report_forms_begin` (
  1035. `begin_id` int(11) NOT NULL AUTO_INCREMENT,
  1036. `user_id` int(11) NULL DEFAULT NULL,
  1037. `report_forms_id` int(11) NULL DEFAULT NULL,
  1038. `begin_customer` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1039. `status` int(2) NULL DEFAULT 99,
  1040. `ctime` datetime(0) NULL DEFAULT NULL,
  1041. `utime` datetime(0) NULL DEFAULT NULL,
  1042. `begin_cumulative` double(16, 2) NULL DEFAULT NULL,
  1043. `admission` tinyint(1) NULL DEFAULT 0,
  1044. PRIMARY KEY (`begin_id`) USING BTREE,
  1045. INDEX `user_id`(`user_id`) USING BTREE,
  1046. INDEX `report_forms_id`(`report_forms_id`) USING BTREE,
  1047. CONSTRAINT `t_report_forms_begin_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  1048. CONSTRAINT `t_report_forms_begin_ibfk_2` FOREIGN KEY (`report_forms_id`) REFERENCES `t_report_forms` (`report_forms_id`) ON DELETE CASCADE ON UPDATE CASCADE
  1049. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  1050. -- ----------------------------
  1051. -- Table structure for t_report_forms_group
  1052. -- ----------------------------
  1053. DROP TABLE IF EXISTS `t_report_forms_group`;
  1054. CREATE TABLE `t_report_forms_group` (
  1055. `report_forms_group_id` int(11) NOT NULL AUTO_INCREMENT,
  1056. `report_forms_group_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1057. `report_forms_group_notes` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1058. `status` int(11) NULL DEFAULT 99,
  1059. `ctime` datetime(0) NULL DEFAULT NULL,
  1060. `utime` datetime(0) NULL DEFAULT NULL,
  1061. `dtime` datetime(0) NULL DEFAULT NULL,
  1062. PRIMARY KEY (`report_forms_group_id`) USING BTREE
  1063. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  1064. -- ----------------------------
  1065. -- Table structure for t_report_forms_permission
  1066. -- ----------------------------
  1067. DROP TABLE IF EXISTS `t_report_forms_permission`;
  1068. CREATE TABLE `t_report_forms_permission` (
  1069. `report_forms_per_id` int(11) NOT NULL AUTO_INCREMENT,
  1070. `report_forms_id` int(11) NOT NULL,
  1071. `user_id` int(11) NOT NULL DEFAULT 0,
  1072. PRIMARY KEY (`report_forms_per_id`) USING BTREE,
  1073. INDEX `user_id`(`user_id`) USING BTREE,
  1074. INDEX `bill_per_id`(`report_forms_per_id`) USING BTREE,
  1075. INDEX `bill_id`(`report_forms_id`) USING BTREE,
  1076. CONSTRAINT `t_report_forms_permission_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  1077. CONSTRAINT `t_report_forms_permission_ibfk_2` FOREIGN KEY (`report_forms_id`) REFERENCES `t_report_forms` (`report_forms_id`) ON DELETE CASCADE ON UPDATE CASCADE
  1078. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  1079. -- ----------------------------
  1080. -- Table structure for t_role
  1081. -- ----------------------------
  1082. DROP TABLE IF EXISTS `t_role`;
  1083. CREATE TABLE `t_role` (
  1084. `role_id` int(11) NOT NULL AUTO_INCREMENT,
  1085. `role_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  1086. `role_desc` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1087. PRIMARY KEY (`role_id`) USING BTREE
  1088. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  1089. -- ----------------------------
  1090. -- Table structure for t_rule_new
  1091. -- ----------------------------
  1092. DROP TABLE IF EXISTS `t_rule_new`;
  1093. CREATE TABLE `t_rule_new` (
  1094. `rule_new_id` int(11) NOT NULL AUTO_INCREMENT,
  1095. `user_id` int(11) NULL DEFAULT NULL,
  1096. `product_modular` tinyint(1) NULL DEFAULT 0,
  1097. `product_modular_delete` tinyint(1) NULL DEFAULT 0,
  1098. `product_modular_add` tinyint(1) NULL DEFAULT 0,
  1099. `product_modular_public` tinyint(1) NULL DEFAULT 0,
  1100. `product_modular_private` tinyint(1) NULL DEFAULT 0,
  1101. `product_modular_supplier` tinyint(1) NULL DEFAULT 0,
  1102. `product_modular_push` tinyint(1) NULL DEFAULT 0,
  1103. `product_modular_template` tinyint(1) NULL DEFAULT 0,
  1104. `product_modular_batch_share` tinyint(1) NULL DEFAULT 0,
  1105. `material_modular` tinyint(1) NULL DEFAULT 0,
  1106. `material_modular_edit` tinyint(1) NULL DEFAULT 0,
  1107. `material_modular_delete` tinyint(1) NULL DEFAULT 0,
  1108. `material_modular_push` tinyint(1) NULL DEFAULT 0,
  1109. `documentary_modular_statistics` tinyint(1) NULL DEFAULT 0,
  1110. `documentary_modular_remove` tinyint(1) NULL DEFAULT 0,
  1111. `product_modular_internal_price` tinyint(1) NULL DEFAULT 0,
  1112. `product_cost_accounting` tinyint(1) NULL DEFAULT 0,
  1113. `product_cost_view` tinyint(1) NULL DEFAULT 0,
  1114. `product_confidential_set` tinyint(1) NULL DEFAULT 0,
  1115. `product_confidential_use` tinyint(1) NULL DEFAULT 0,
  1116. `customer_modular_group_secrecy` tinyint(1) NULL DEFAULT 0,
  1117. `product_watermark` tinyint(1) NULL DEFAULT 0,
  1118. `customer_modular` tinyint(1) NULL DEFAULT 0,
  1119. `customer_modular_delete` tinyint(1) NULL DEFAULT 0,
  1120. `customer_modular_customer_edit` tinyint(1) NULL DEFAULT 0,
  1121. `customer_modular_company_edit` tinyint(1) NULL DEFAULT 0,
  1122. `customer_modular_add` tinyint(1) NULL DEFAULT 0,
  1123. `customer_modular_transfer` tinyint(1) NULL DEFAULT 0,
  1124. `customer_modular_set_visible` tinyint(1) NULL DEFAULT 0,
  1125. `machine_modular` tinyint(1) NULL DEFAULT 0,
  1126. `machine_modular_add` tinyint(1) NULL DEFAULT 0,
  1127. `machine_modular_edit` tinyint(1) NULL DEFAULT 0,
  1128. `machine_modular_record` tinyint(1) NULL DEFAULT 0,
  1129. `machine_modular_wait` tinyint(1) NULL DEFAULT 0,
  1130. `machine_modular_pause` tinyint(1) NULL DEFAULT 0,
  1131. `shop_modular` tinyint(1) NULL DEFAULT 0,
  1132. `shop_modular_update` tinyint(1) NULL DEFAULT 0,
  1133. `bill_modular` tinyint(1) NULL DEFAULT 0,
  1134. `bill_modular_waste` tinyint(1) NULL DEFAULT 0,
  1135. `bill_modular_set` tinyint(1) NULL DEFAULT 0,
  1136. `affair_modular` tinyint(1) NULL DEFAULT 0,
  1137. `employee_modular` tinyint(1) NULL DEFAULT 0,
  1138. `tag_modular` tinyint(1) NULL DEFAULT 0,
  1139. `report_modular` tinyint(1) NULL DEFAULT 0,
  1140. `report_modular_download` tinyint(1) NULL DEFAULT 0,
  1141. `company_modular` tinyint(1) NULL DEFAULT 0,
  1142. `company_modular_authentication` tinyint(1) NULL DEFAULT 0,
  1143. `affair_recycle_bin` tinyint(1) NULL DEFAULT 0,
  1144. `documentary_modular` tinyint(1) NULL DEFAULT 0,
  1145. `documentary_modular_control` tinyint(1) NULL DEFAULT 0,
  1146. `documentary_modular_report` tinyint(1) NULL DEFAULT 0,
  1147. `print_modular` tinyint(1) NULL DEFAULT 0,
  1148. `documentary_modular_adjustment` tinyint(1) NULL DEFAULT 0,
  1149. `product_modular_public_see` tinyint(1) NULL DEFAULT 0,
  1150. `product_modular_private_see` tinyint(1) NULL DEFAULT 0,
  1151. `affair_all_modify_record_see` tinyint(1) NULL DEFAULT 0,
  1152. `inventory_modular_edit` tinyint(1) NULL DEFAULT 0,
  1153. `image_search` tinyint(1) NULL DEFAULT 0,
  1154. PRIMARY KEY (`rule_new_id`) USING BTREE,
  1155. UNIQUE INDEX `rule_new_id`(`rule_new_id`) USING BTREE,
  1156. INDEX `user_id`(`user_id`) USING BTREE,
  1157. CONSTRAINT `t_rule_new_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
  1158. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  1159. -- ----------------------------
  1160. -- Table structure for t_seal_account
  1161. -- ----------------------------
  1162. DROP TABLE IF EXISTS `t_seal_account`;
  1163. CREATE TABLE `t_seal_account` (
  1164. `seal_id` int(11) NOT NULL AUTO_INCREMENT,
  1165. `user_id` int(11) NULL DEFAULT NULL,
  1166. `content_id` int(11) NULL DEFAULT NULL,
  1167. `content_type` tinyint(1) NULL DEFAULT 0,
  1168. `content_time` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1169. PRIMARY KEY (`seal_id`) USING BTREE,
  1170. INDEX `user_id`(`user_id`) USING BTREE,
  1171. CONSTRAINT `t_seal_account_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
  1172. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  1173. -- ----------------------------
  1174. -- Table structure for t_shop
  1175. -- ----------------------------
  1176. DROP TABLE IF EXISTS `t_shop`;
  1177. CREATE TABLE `t_shop` (
  1178. `shop_id` int(11) NOT NULL AUTO_INCREMENT,
  1179. `company_id` int(11) NULL DEFAULT NULL,
  1180. `shop_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1181. `shop_urlname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1182. `shop_intro` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1183. `shop_allow_subdomain` smallint(6) NULL DEFAULT 0,
  1184. `shop_open_time` datetime(0) NULL DEFAULT NULL,
  1185. `shop_hits` int(255) NULL DEFAULT 0,
  1186. `shop_wechat_hits` int(255) NULL DEFAULT 0,
  1187. PRIMARY KEY (`shop_id`) USING BTREE
  1188. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
  1189. -- ----------------------------
  1190. -- Table structure for t_shop_product
  1191. -- ----------------------------
  1192. DROP TABLE IF EXISTS `t_shop_product`;
  1193. CREATE TABLE `t_shop_product` (
  1194. `product_id` int(11) NOT NULL,
  1195. `company_id` int(11) NULL DEFAULT NULL,
  1196. `user_id` int(11) NULL DEFAULT NULL,
  1197. `shop_id` int(11) NULL DEFAULT NULL,
  1198. `product_shop_pname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1199. `product_shop_material` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1200. `product_shop_height` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1201. `product_shop_height_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1202. `product_shop_width` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1203. `product_shop_width_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1204. `product_shop_elasticity` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1205. `product_shop_price_origin` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1206. `product_shop_price_origin_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1207. `product_shop_price_on_sale` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1208. `product_shop_price_on_sale_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1209. `product_shop_inventory` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1210. `product_shop_inventory_unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1211. `product_intro` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1212. `product_is_promotion` int(11) NULL DEFAULT NULL,
  1213. `product_is_order` int(255) NULL DEFAULT NULL,
  1214. `product_on_time` datetime(0) NULL DEFAULT NULL,
  1215. `product_tag_ids` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1216. `product_order` int(255) NULL DEFAULT 0,
  1217. `product_promotion` tinyint(1) NULL DEFAULT NULL,
  1218. `product_promotion_start` datetime(0) NULL DEFAULT NULL,
  1219. `product_promotion_end` datetime(0) NULL DEFAULT NULL,
  1220. `product_shop_hits` int(255) UNSIGNED NULL DEFAULT 0,
  1221. `product_shop_hits_weight` int(11) NULL DEFAULT 0,
  1222. PRIMARY KEY (`product_id`) USING BTREE,
  1223. UNIQUE INDEX `product_id`(`product_id`) USING BTREE,
  1224. CONSTRAINT `t_shop_product_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `t_product` (`product_id`) ON DELETE CASCADE ON UPDATE RESTRICT
  1225. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
  1226. -- ----------------------------
  1227. -- Table structure for t_statement_calculation
  1228. -- ----------------------------
  1229. DROP TABLE IF EXISTS `t_statement_calculation`;
  1230. CREATE TABLE `t_statement_calculation` (
  1231. `calculation_id` int(11) NOT NULL AUTO_INCREMENT,
  1232. `data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  1233. `affair_id` int(11) NOT NULL,
  1234. `user_id` int(11) NOT NULL,
  1235. `tagIds` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  1236. `bill_order_id` int(11) NOT NULL,
  1237. `calculation_key` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  1238. `calculation_time` datetime(0) NOT NULL,
  1239. `calculation_product_specification` varchar(255) NOT NULL,
  1240. `calculation_product_color` varchar(255) NOT NULL,
  1241. `calculation_account_category_out` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1242. `calculation_receivables_payment_category` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1243. `receivables_mode` tinyint(1) NOT NULL,
  1244. `bill_order_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  1245. `tag_numbers` tinyint(1) NULL DEFAULT 0,
  1246. `bill_ids` text NULL,
  1247. `calculation_value` decimal(22, 6) NOT NULL DEFAULT 0.000000,
  1248. `calculation_condition` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1249. `calculation_supplement` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  1250. `call_time` datetime(0) NOT NULL,
  1251. `calculation_senior_screen` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1252. `calculation_account_category` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1253. `calculation_product_id` int(11) NOT NULL,
  1254. `calculation_collection_id` int(11) NOT NULL,
  1255. `day` INT ( 11 ) NULL,
  1256. `calculation_ctime` datetime(0) NULL DEFAULT NULL,
  1257. `calculation_note` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1258. PRIMARY KEY (`calculation_id`, `data_id`) USING BTREE,
  1259. INDEX (`bill_order_number`) USING BTREE,
  1260. INDEX `bill_order_id`(`bill_order_id`) USING BTREE,
  1261. INDEX `call_time`(`call_time`) USING BTREE,
  1262. INDEX ` calculation_value`(`calculation_key`, `affair_id`, `calculation_time`, `calculation_value`, `calculation_condition`, `tagIds`) USING BTREE,
  1263. INDEX `calculation_time`(`calculation_time`, `affair_id`, `calculation_condition`, `tagIds`) USING BTREE
  1264. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  1265. -- ----------------------------
  1266. -- Table structure for t_tag_frequently
  1267. -- ----------------------------
  1268. DROP TABLE IF EXISTS `t_tag_frequently`;
  1269. CREATE TABLE `t_tag_frequently` (
  1270. `frequently_id` int(11) NOT NULL AUTO_INCREMENT,
  1271. `user_id` int(11) NOT NULL,
  1272. `tag_id` int(11) NOT NULL,
  1273. `ctime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  1274. PRIMARY KEY (`frequently_id`) USING BTREE,
  1275. INDEX `user_id`(`user_id`) USING BTREE,
  1276. INDEX `tag_id`(`tag_id`) USING BTREE,
  1277. CONSTRAINT `t_tag_frequently_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  1278. CONSTRAINT `t_tag_frequently_ibfk_3` FOREIGN KEY (`tag_id`) REFERENCES `t_tag_list` (`tag_id`) ON DELETE CASCADE ON UPDATE CASCADE
  1279. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;
  1280. -- ----------------------------
  1281. -- Table structure for t_tag_list
  1282. -- ----------------------------
  1283. DROP TABLE IF EXISTS `t_tag_list`;
  1284. CREATE TABLE `t_tag_list` (
  1285. `tag_id` int(11) NOT NULL AUTO_INCREMENT,
  1286. `company_id` int(11) NULL DEFAULT NULL,
  1287. `user_id` int(11) NULL DEFAULT NULL,
  1288. `tag_category` int(11) NULL DEFAULT 0,
  1289. `tag_image_id` int(11) NULL DEFAULT NULL,
  1290. `tag_image` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1291. `tag_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1292. `tag_desc` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1293. `used_count` int(11) NULL DEFAULT 0,
  1294. `status` int(2) NULL DEFAULT 99,
  1295. `ctime` datetime(0) NULL DEFAULT NULL,
  1296. `utime` datetime(0) NULL DEFAULT NULL,
  1297. `permission` int(1) NULL DEFAULT 1,
  1298. `necessary` int(1) NULL DEFAULT 0,
  1299. `data_protection` int(1) NULL DEFAULT 0,
  1300. `position` int(11) NULL DEFAULT 0,
  1301. `permission_view` INT ( 1 ) NULL DEFAULT 1,
  1302. `tag_group_id` int(11) NULL DEFAULT 0,
  1303. PRIMARY KEY (`tag_id`) USING BTREE,
  1304. INDEX `t_tag_list_ibfk_1`(`tag_image_id`) USING BTREE
  1305. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  1306. -- ----------------------------
  1307. -- Table structure for t_tag_list_rel
  1308. -- ----------------------------
  1309. DROP TABLE IF EXISTS `t_tag_list_rel`;
  1310. CREATE TABLE `t_tag_list_rel` (
  1311. `rel_id` int(11) NOT NULL AUTO_INCREMENT,
  1312. `user_id` int(11) NOT NULL,
  1313. `tag_category` int(11) NULL DEFAULT NULL,
  1314. `content_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1315. `content_type` int(11) NULL DEFAULT 0,
  1316. `tag_id` int(11) NULL DEFAULT NULL,
  1317. `ctime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  1318. PRIMARY KEY (`rel_id`) USING BTREE,
  1319. INDEX `tag_id`(`tag_id`) USING BTREE,
  1320. CONSTRAINT `t_tag_list_rel_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `t_tag_list` (`tag_id`) ON DELETE CASCADE ON UPDATE CASCADE
  1321. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  1322. -- ----------------------------
  1323. -- Table structure for t_tag_permission
  1324. -- ----------------------------
  1325. DROP TABLE IF EXISTS `t_tag_permission`;
  1326. CREATE TABLE `t_tag_permission` (
  1327. `per_id` int(11) NOT NULL AUTO_INCREMENT,
  1328. `tag_id` int(11) NOT NULL,
  1329. `user_id` int(11) NOT NULL DEFAULT 0,
  1330. PRIMARY KEY (`per_id`) USING BTREE,
  1331. INDEX `per_id`(`per_id`) USING BTREE,
  1332. INDEX `tag_id`(`tag_id`) USING BTREE,
  1333. INDEX `user_id`(`user_id`) USING BTREE,
  1334. `allow` INT ( 1 ) NULL DEFAULT 1,
  1335. CONSTRAINT `t_tag_permission_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `t_tag_list` (`tag_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  1336. CONSTRAINT `t_tag_permission_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
  1337. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  1338. -- ----------------------------
  1339. -- Table structure for t_user
  1340. -- ----------------------------
  1341. DROP TABLE IF EXISTS `t_user`;
  1342. CREATE TABLE `t_user` (
  1343. `user_id` int(11) NOT NULL AUTO_INCREMENT,
  1344. `company_id` int(11) NULL DEFAULT NULL,
  1345. `role_id` int(11) NOT NULL,
  1346. `location_id` int(11) NULL DEFAULT NULL,
  1347. `department_id` int(11) NULL DEFAULT NULL,
  1348. `user_addr` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1349. `user_company_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1350. `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  1351. `user_password` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  1352. `user_pwdhash` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1353. `user_realname` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  1354. `user_gender` tinyint(1) NULL DEFAULT 1,
  1355. `user_avatar` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1356. `user_phonenum` varchar(11) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1357. `user_state` tinyint(1) NULL DEFAULT 0,
  1358. `user_qq` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1359. `user_email` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1360. `user_uid` varchar(18) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1361. `user_region` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1362. `visible` tinyint(255) NULL DEFAULT NULL,
  1363. `ctime` datetime(0) NOT NULL,
  1364. `utime` datetime(0) NULL DEFAULT NULL,
  1365. `dtime` datetime(0) NULL DEFAULT NULL,
  1366. `status` decimal(2, 0) NOT NULL DEFAULT 99,
  1367. `user_logtimes` int(11) NULL DEFAULT NULL,
  1368. `user_lastip` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1369. `user_lastlogintime` datetime(0) NULL DEFAULT NULL,
  1370. `user_lastackqstring` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1371. `user_lastackurl` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1372. `avos_session_token` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1373. `user_lastacktime` datetime(0) NULL DEFAULT NULL,
  1374. `user_lastackdevice` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1375. `user_signup_user_agent` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1376. `auth_key` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1377. `access_token` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1378. `tgt` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1379. `st` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1380. `sid` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1381. `avos_token` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1382. `referral_code` int(6) NULL DEFAULT NULL,
  1383. `user_lastaddress` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1384. `necessary` tinyint(1) NULL DEFAULT 0,
  1385. `work_time` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1386. `attendance_id` int(11) NULL DEFAULT NULL,
  1387. `bill_header` varchar(255) NULL,
  1388. `report_header` varchar(255) NULL,
  1389. `report_password` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NULL,
  1390. `report_password_type` tinyint(1) NULL DEFAULT 0,
  1391. PRIMARY KEY (`user_id`) USING BTREE,
  1392. INDEX `FK_Relationship_2`(`role_id`) USING BTREE,
  1393. INDEX `company_id`(`company_id`) USING BTREE,
  1394. INDEX `department_id`(`department_id`) USING BTREE,
  1395. INDEX `user_name`(`user_name`) USING BTREE,
  1396. INDEX `referral_code`(`referral_code`) USING BTREE,
  1397. INDEX `referral_code_2`(`referral_code`) USING BTREE,
  1398. INDEX `user_phonenum`(`user_phonenum`) USING BTREE,
  1399. CONSTRAINT `FK_Relationship_2` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  1400. CONSTRAINT `t_user_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `t_company` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  1401. CONSTRAINT `t_user_ibfk_3` FOREIGN KEY (`department_id`) REFERENCES `t_department` (`department_id`) ON DELETE SET NULL ON UPDATE CASCADE
  1402. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  1403. -- ----------------------------
  1404. -- Table structure for t_user_attendance
  1405. -- ----------------------------
  1406. DROP TABLE IF EXISTS `t_user_attendance`;
  1407. CREATE TABLE `t_user_attendance` (
  1408. `attendance_id` int(11) NOT NULL AUTO_INCREMENT,
  1409. `attendance_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  1410. `attendance_time` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  1411. `monday` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  1412. `tuesday` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',
  1413. `wednesday` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  1414. `thursday` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  1415. `friday` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  1416. `saturday` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  1417. `sunday` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  1418. PRIMARY KEY (`attendance_id`) USING BTREE
  1419. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  1420. -- ----------------------------
  1421. -- Table structure for t_work
  1422. -- ----------------------------
  1423. DROP TABLE IF EXISTS `t_work`;
  1424. CREATE TABLE `t_work` (
  1425. `work_id` int(11) NOT NULL AUTO_INCREMENT,
  1426. `work_pid` int(11) NULL DEFAULT NULL,
  1427. `work_children_ids` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1428. `affair_id` int(11) NULL DEFAULT NULL,
  1429. `user_id` int(11) NULL DEFAULT NULL,
  1430. `work_type` int(11) NULL DEFAULT NULL,
  1431. `reminder_id` int(11) NULL DEFAULT NULL,
  1432. `image_ids` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1433. `voice_ids` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1434. `content_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1435. `ctime` datetime(0) NULL DEFAULT NULL,
  1436. `utime` datetime(0) NULL DEFAULT NULL,
  1437. `work_data_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1438. `work_object_type` tinyint(1) NULL DEFAULT 1,
  1439. `work_object_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1440. `work_theme` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1441. `affair_data_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1442. PRIMARY KEY (`work_id`) USING BTREE
  1443. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  1444. -- ----------------------------
  1445. -- Table structure for t_work_frequently
  1446. -- ----------------------------
  1447. DROP TABLE IF EXISTS `t_work_frequently`;
  1448. CREATE TABLE `t_work_frequently` (
  1449. `work_frequently_id` int(11) NOT NULL AUTO_INCREMENT,
  1450. `user_id` int(11) NOT NULL,
  1451. `affair_id` int(11) NOT NULL,
  1452. `ctime` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  1453. `work_type_notice` tinyint(1) NULL DEFAULT 0,
  1454. `work_type_instructions` tinyint(1) NULL DEFAULT 0,
  1455. `work_type_Assign` tinyint(1) NULL DEFAULT 0,
  1456. PRIMARY KEY (`work_frequently_id`) USING BTREE,
  1457. INDEX `affair_id`(`affair_id`) USING BTREE,
  1458. INDEX `user_id`(`user_id`) USING BTREE,
  1459. CONSTRAINT `t_work_frequently_ibfk_1` FOREIGN KEY (`affair_id`) REFERENCES `t_affair` (`affair_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  1460. CONSTRAINT `t_work_frequently_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
  1461. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;
  1462. -- ----------------------------
  1463. -- Table structure for t_work_reminder
  1464. -- ----------------------------
  1465. DROP TABLE IF EXISTS `t_work_reminder`;
  1466. CREATE TABLE `t_work_reminder` (
  1467. `work_id` int(11) NOT NULL,
  1468. `reply_id` int(11) NULL DEFAULT NULL,
  1469. `set_object_type` tinyint(1) NULL DEFAULT NULL,
  1470. `set_object_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1471. `set_frequency` int(11) NULL DEFAULT 0,
  1472. `set_interval` int(11) NULL DEFAULT NULL,
  1473. `set_interval_unit` tinyint(1) NULL DEFAULT NULL,
  1474. `reminder_object_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1475. `reminder_frequency` int(11) NULL DEFAULT 0,
  1476. `reminder_time` datetime(0) NULL DEFAULT NULL,
  1477. `is_reminder` tinyint(1) NULL DEFAULT 0,
  1478. PRIMARY KEY (`work_id`) USING BTREE,
  1479. CONSTRAINT `t_work_reminder_ibfk_1` FOREIGN KEY (`work_id`) REFERENCES `t_work` (`work_id`) ON DELETE CASCADE ON UPDATE CASCADE
  1480. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  1481. -- ----------------------------
  1482. -- Table structure for t_work_reply
  1483. -- ----------------------------
  1484. DROP TABLE IF EXISTS `t_work_reply`;
  1485. CREATE TABLE `t_work_reply` (
  1486. `reply_id` int(11) NOT NULL AUTO_INCREMENT,
  1487. `reply_pid` int(11) NULL DEFAULT NULL,
  1488. `work_id` int(11) NULL DEFAULT NULL,
  1489. `reply_user_id` int(11) NULL DEFAULT NULL,
  1490. `reply_txt` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1491. `reply_affair_old_data` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1492. `reply_affair_new_data` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1493. `reply_time` datetime(0) NULL DEFAULT NULL,
  1494. `reply_object_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1495. `reply_image` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1496. `reply_voice` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1497. `reply_content` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1498. PRIMARY KEY (`reply_id`) USING BTREE,
  1499. INDEX `work_id`(`work_id`) USING BTREE,
  1500. CONSTRAINT `t_work_reply_ibfk_1` FOREIGN KEY (`work_id`) REFERENCES `t_work` (`work_id`) ON DELETE CASCADE ON UPDATE CASCADE
  1501. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  1502. -- ----------------------------
  1503. -- Table structure for t_process_in_calculation
  1504. -- ----------------------------
  1505. DROP TABLE IF EXISTS `t_process_in_calculation`;
  1506. CREATE TABLE `t_process_in_calculation` (
  1507. `calculation_id` int(11) NOT NULL AUTO_INCREMENT,
  1508. `data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1509. `affair_id` int(11) NOT NULL,
  1510. `tagIds` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1511. `calculation_key` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1512. `calculation_time` datetime(0) NOT NULL,
  1513. `product_id` int(11) NOT NULL,
  1514. `collection_id` int(11) NOT NULL,
  1515. `product_color` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1516. `product_color_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1517. `sale_object` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1518. `product_width` decimal(14, 2) NOT NULL,
  1519. `product_height` decimal(14, 2) NOT NULL,
  1520. `machining_price` decimal(15, 3) NOT NULL,
  1521. `calculation_condition` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1522. `actual_in_storehouse_quantity` decimal(22, 2) NOT NULL,
  1523. `call_time` datetime(0) NOT NULL,
  1524. `tag_numbers` tinyint(1) NULL DEFAULT 0,
  1525. `user_id` int(11) NOT NULL,
  1526. PRIMARY KEY (`calculation_id`, `data_id`) USING BTREE,
  1527. INDEX `call_time`(`call_time`) USING BTREE,
  1528. INDEX `data_id`(`data_id`) USING BTREE,
  1529. INDEX `affair_id`(`affair_id`, `calculation_time`, `calculation_condition`, `tagIds`, `calculation_key`, `product_id`, `product_color`, `sale_object`) USING BTREE
  1530. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  1531. -- ----------------------------
  1532. -- Table structure for t_process_out_calculation
  1533. -- ----------------------------
  1534. DROP TABLE IF EXISTS `t_process_out_calculation`;
  1535. CREATE TABLE `t_process_out_calculation` (
  1536. `calculation_id` int(11) NOT NULL AUTO_INCREMENT,
  1537. `data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1538. `affair_id` int(11) NOT NULL,
  1539. `tagIds` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1540. `calculation_condition` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1541. `calculation_time` datetime(0) NOT NULL,
  1542. `machining_price` decimal(15, 3) NOT NULL,
  1543. `machining_quantity` decimal(15, 2) NOT NULL,
  1544. `machining_total_amount` decimal(15, 3) NOT NULL,
  1545. `out_storehouse_quantity` decimal(22, 2) NOT NULL,
  1546. `from_data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1547. `is_complete` int(1) NOT NULL DEFAULT 0,
  1548. `call_time` datetime(0) NOT NULL,
  1549. `clearing_storehouse` int(1) NOT NULL DEFAULT 0,
  1550. `tag_numbers` tinyint(1) NULL DEFAULT 0,
  1551. `user_id` int(11) NOT NULL,
  1552. PRIMARY KEY (`calculation_id`, `data_id`, `from_data_id`) USING BTREE,
  1553. INDEX `call_time`(`call_time`) USING BTREE,
  1554. INDEX `affair_id`(`affair_id`, `calculation_time`, `calculation_condition`, `tagIds`, `from_data_id`) USING BTREE
  1555. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  1556. -- ----------------------------
  1557. -- Table structure for t_machine_calculation
  1558. -- ----------------------------
  1559. DROP TABLE IF EXISTS `t_machine_calculation`;
  1560. CREATE TABLE `t_machine_calculation` (
  1561. `calculation_id` int(11) NOT NULL AUTO_INCREMENT,
  1562. `data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  1563. `affair_id` int(11) NOT NULL,
  1564. `tagIds` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  1565. `calculation_condition` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
  1566. `call_time` datetime(0) NOT NULL,
  1567. `calculation_key` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  1568. `calculation_time` datetime(0) NOT NULL ,
  1569. `calculation_time2` datetime(0) NOT NULL ,
  1570. `product_id` int(11) NOT NULL DEFAULT 0,
  1571. `single_weight` decimal(22, 2) NULL DEFAULT 0.00 ,
  1572. `pipe_weight` decimal(22, 2) NULL DEFAULT 0.00 ,
  1573. `machine_horse` bigint(22) NULL DEFAULT NULL,
  1574. `pan_head_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1575. `public_note` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1576. `stop_reason_id` int(11) NULL,
  1577. `object_key` varchar(25) NULL,
  1578. `calculation_value` decimal(22,2) NULL,
  1579. `needle_position` int(11) NULL,
  1580. `net_needle` int(11) NULL,
  1581. `product_specification` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  1582. `machine_production_state` tinyint(1) NULL DEFAULT 0,
  1583. PRIMARY KEY (`calculation_id`, `data_id`) USING BTREE,
  1584. INDEX `call_time`(`call_time`) USING BTREE,
  1585. INDEX ` calculation_value`(`affair_id`, `calculation_key`, `calculation_condition`, `calculation_time`, `product_id`, `tagIds`, `calculation_time2`) USING BTREE
  1586. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  1587. -- ----------------------------
  1588. -- Table structure for t_ticket_calculation
  1589. -- ----------------------------
  1590. DROP TABLE IF EXISTS `t_ticket_calculation`;
  1591. CREATE TABLE `t_ticket_calculation` (
  1592. `calculation_id` int(11) NOT NULL AUTO_INCREMENT,
  1593. `data_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  1594. `affair_id` int(11) NOT NULL,
  1595. `tagIds` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  1596. `calculation_condition` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
  1597. `calculation_key` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  1598. `calculation_time` datetime(0) NOT NULL ,
  1599. `calculation_value` decimal(22, 6) NOT NULL DEFAULT 0.000000 ,
  1600. `call_time` datetime(0) NOT NULL,
  1601. `calculation_note` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1602. `tag_numbers` tinyint(1) NULL DEFAULT 0,
  1603. `ticket_key` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  1604. `ticket_time` datetime(0) NOT NULL ,
  1605. PRIMARY KEY (`calculation_id`, `data_id`) USING BTREE,
  1606. INDEX `call_time`(`call_time`) USING BTREE,
  1607. INDEX ` calculation_value`(`affair_id`, `calculation_key`, `calculation_condition`, `calculation_time`, `tagIds`, `ticket_key`, `ticket_time`) USING BTREE
  1608. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  1609. -- ----------------------------
  1610. -- Table structure for t_customer_given
  1611. -- ----------------------------
  1612. DROP TABLE IF EXISTS `t_customer_given`;
  1613. CREATE TABLE `t_customer_given` (
  1614. `id` int(11) NOT NULL AUTO_INCREMENT,
  1615. `givenKey` varchar(50) NOT NULL,
  1616. `companyId` int(11) DEFAULT NULL,
  1617. PRIMARY KEY (`id`)
  1618. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  1619. -- ----------------------------
  1620. -- Table structure for t_customer_visible
  1621. -- ----------------------------
  1622. DROP TABLE IF EXISTS `t_customer_visible`;
  1623. CREATE TABLE `t_customer_visible` (
  1624. `id` int(20) NOT NULL AUTO_INCREMENT,
  1625. `userId` int(11) NOT NULL,
  1626. `objectKey` varchar(50) NOT NULL,
  1627. `companyId` int(11) DEFAULT NULL,
  1628. PRIMARY KEY (`id`),
  1629. KEY `userId` (`userId`,`objectKey`) USING BTREE,
  1630. CONSTRAINT `t_customer_visible_ibfk_2` FOREIGN KEY (`userId`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
  1631. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  1632. -- ----------------------------
  1633. -- Table structure for t_documentary_statistics
  1634. -- ----------------------------
  1635. DROP TABLE IF EXISTS `t_documentary_statistics`;
  1636. CREATE TABLE `t_documentary_statistics` (
  1637. `statistics_id` int(11) NOT NULL AUTO_INCREMENT,
  1638. `statistics_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  1639. `statistics_number` int(11) NOT NULL DEFAULT 0,
  1640. `permission` tinyint(1) NULL DEFAULT 0,
  1641. `user_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1642. `companyId` int(11) NULL DEFAULT NULL,
  1643. PRIMARY KEY (`statistics_id`) USING BTREE
  1644. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  1645. -- ----------------------------
  1646. -- Table structure for t_documentary_step
  1647. -- ----------------------------
  1648. DROP TABLE IF EXISTS `t_documentary_step`;
  1649. CREATE TABLE `t_documentary_step` (
  1650. `step_id` int(11) NOT NULL AUTO_INCREMENT,
  1651. `step_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  1652. `step_number` int(11) NOT NULL,
  1653. `affairIds` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1654. `companyId` int(11) NULL DEFAULT 0,
  1655. `ctime` datetime(0) NULL DEFAULT NULL,
  1656. `utime` datetime(0) NULL DEFAULT NULL,
  1657. `tag_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1658. PRIMARY KEY (`step_id`) USING BTREE
  1659. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
  1660. -- ----------------------------
  1661. -- Table structure for t_report_account
  1662. -- ----------------------------
  1663. DROP TABLE IF EXISTS `t_report_account`;
  1664. CREATE TABLE `t_report_account` (
  1665. `account_id` int(11) NOT NULL AUTO_INCREMENT,
  1666. `account_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  1667. `account_bank_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  1668. `account_bank_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  1669. `account_cumulative` double(13, 2) NULL DEFAULT 0,
  1670. `ctime` datetime(0) NULL DEFAULT NULL,
  1671. `utime` datetime(0) NULL DEFAULT NULL,
  1672. `user_id` int(11) NULL DEFAULT NULL,
  1673. `permission` tinyint(1) NULL DEFAULT 1,
  1674. `see_user_ids` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  1675. PRIMARY KEY (`account_id`) USING BTREE
  1676. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  1677. -- ----------------------------
  1678. -- Table structure for t_inventory_frequently
  1679. -- ----------------------------
  1680. DROP TABLE IF EXISTS `t_inventory_frequently`;
  1681. CREATE TABLE `t_inventory_frequently` (
  1682. `frequently_id` int(11) NOT NULL AUTO_INCREMENT,
  1683. `user_id` int(11) NULL DEFAULT NULL,
  1684. `inventory_user_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1685. `utime` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  1686. UNIQUE INDEX `frequently_id`(`frequently_id`) USING BTREE
  1687. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;
  1688. -- ----------------------------
  1689. -- Table structure for t_inventory_notice
  1690. -- ----------------------------
  1691. DROP TABLE IF EXISTS `t_inventory_notice`;
  1692. CREATE TABLE `t_inventory_notice` (
  1693. `notice_id` int(11) NOT NULL AUTO_INCREMENT,
  1694. `user_id` int(11) NULL DEFAULT NULL,
  1695. `data_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1696. `ctime` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  1697. `content` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1698. `data_count` int(11) NULL DEFAULT NULL,
  1699. PRIMARY KEY (`notice_id`) USING BTREE,
  1700. UNIQUE INDEX `frequently_id`(`notice_id`) USING BTREE
  1701. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;
  1702. -- ----------------------------
  1703. -- Table structure for t_inventory_notice_user
  1704. -- ----------------------------
  1705. DROP TABLE IF EXISTS `t_inventory_notice_user`;
  1706. CREATE TABLE `t_inventory_notice_user` (
  1707. `id` int(11) NOT NULL AUTO_INCREMENT,
  1708. `notice_id` int(11) NOT NULL,
  1709. `user_id` int(11) NOT NULL,
  1710. `ctime` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  1711. `notice_state` tinyint(1) NULL DEFAULT NULL,
  1712. PRIMARY KEY (`id`) USING BTREE,
  1713. UNIQUE INDEX `id`(`id`) USING BTREE
  1714. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;
  1715. -- ----------------------------
  1716. -- Table structure for t_screen
  1717. -- ----------------------------
  1718. DROP TABLE IF EXISTS `t_screen`;
  1719. CREATE TABLE `t_screen` (
  1720. `screen_id` int(11) NOT NULL AUTO_INCREMENT,
  1721. `user_id` int(11) NULL DEFAULT NULL,
  1722. `column_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  1723. `utime` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '',
  1724. `screen_type` tinyint(1) NULL DEFAULT 1,
  1725. UNIQUE INDEX `frequently_id`(`screen_id`) USING BTREE
  1726. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;
  1727. -- ----------------------------
  1728. -- Table structure for t_user_reminder
  1729. -- ----------------------------
  1730. DROP TABLE IF EXISTS `t_user_reminder`;
  1731. CREATE TABLE `t_user_reminder` (
  1732. `user_id` int(11) NOT NULL,
  1733. `sale_price_reminder` tinyint(1) DEFAULT 0,
  1734. `purchase_price_reminder` tinyint(1) DEFAULT 0,
  1735. `documentary_add_reminder` tinyint(1) DEFAULT 0,
  1736. `documentary_add_son_reminder` tinyint(1) DEFAULT 0,
  1737. `documentary_join_reminder` tinyint(1) DEFAULT 0,
  1738. `documentary_new_affair_join_reminder` tinyint(1) NULL DEFAULT 0,
  1739. `product_image_shear` tinyint(1) NULL DEFAULT 0,
  1740. `documentary_copy_order` tinyint(1) NULL DEFAULT 0,
  1741. `customer_tag_reminder` tinyint(1) NULL DEFAULT 0,
  1742. PRIMARY KEY (`user_id`),
  1743. UNIQUE KEY `user_id` (`user_id`) USING BTREE
  1744. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT;
  1745. -- ----------------------------
  1746. -- Table structure for t_notification
  1747. -- ----------------------------
  1748. DROP TABLE IF EXISTS `t_notification`;
  1749. CREATE TABLE `t_notification` (
  1750. `notification_id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT,
  1751. `notification_code` int(11) NULL,
  1752. `is_read` int(2) NULL,
  1753. `ctime` datetime(0) NULL,
  1754. PRIMARY KEY (`notification_id`)
  1755. );
  1756. -- ----------------------------
  1757. -- Table structure for t_confidential_cabinet
  1758. -- ----------------------------
  1759. DROP TABLE IF EXISTS `t_confidential_cabinet`;
  1760. CREATE TABLE `t_confidential_cabinet` (
  1761. `cabinet_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '公司单号ID',
  1762. `cabinet_name` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '',
  1763. `permission` tinyint(1) DEFAULT 0,
  1764. `user_ids` text COLLATE utf8_bin DEFAULT NULL,
  1765. `user_id` int(11) DEFAULT NULL,
  1766. `company_id` int(11) DEFAULT NULL,
  1767. `ctime` datetime DEFAULT NULL,
  1768. `utime` datetime DEFAULT NULL,
  1769. PRIMARY KEY (`cabinet_id`)
  1770. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  1771. -- ----------------------------
  1772. -- Table structure for t_confidential_lock
  1773. -- ----------------------------
  1774. DROP TABLE IF EXISTS `t_confidential_lock`;
  1775. CREATE TABLE `t_confidential_lock` (
  1776. `lock_id` int(11) NOT NULL AUTO_INCREMENT,
  1777. `lock_name` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '',
  1778. `lock_password` varchar(50) COLLATE utf8_bin NOT NULL DEFAULT '',
  1779. `lock_time` varchar(25) COLLATE utf8_bin DEFAULT '',
  1780. `user_id` int(11) DEFAULT NULL,
  1781. `company_id` int(11) DEFAULT NULL,
  1782. `ctime` datetime DEFAULT NULL,
  1783. `utime` datetime DEFAULT NULL,
  1784. PRIMARY KEY (`lock_id`)
  1785. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  1786. -- ----------------------------
  1787. -- Table structure for t_confidential_relation
  1788. -- ----------------------------
  1789. DROP TABLE IF EXISTS `t_confidential_relation`;
  1790. CREATE TABLE `t_confidential_relation` (
  1791. `relation_id` int(11) NOT NULL AUTO_INCREMENT,
  1792. `lock_id` int(11) NOT NULL,
  1793. `cabinet_id` int(11) NOT NULL,
  1794. PRIMARY KEY (`relation_id`),
  1795. KEY `lock_id` (`lock_id`),
  1796. KEY `cabinet_id` (`cabinet_id`),
  1797. CONSTRAINT `t_confidential_relation_ibfk_1` FOREIGN KEY (`lock_id`) REFERENCES `t_confidential_lock` (`lock_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  1798. CONSTRAINT `t_confidential_relation_ibfk_2` FOREIGN KEY (`cabinet_id`) REFERENCES `t_confidential_cabinet` (`cabinet_id`) ON DELETE CASCADE ON UPDATE CASCADE
  1799. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  1800. -- ----------------------------
  1801. -- Table structure for t_product_share_error
  1802. -- ----------------------------
  1803. DROP TABLE IF EXISTS `t_product_share_error`;
  1804. CREATE TABLE `t_product_share_error` (
  1805. `error_id` int(11) NOT NULL AUTO_INCREMENT,
  1806. `error_ip` varchar(30) COLLATE utf8_bin NOT NULL DEFAULT '',
  1807. `error_number` tinyint(1) DEFAULT NULL,
  1808. `error_time` datetime DEFAULT NULL,
  1809. PRIMARY KEY (`error_id`)
  1810. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  1811. -- ----------------------------
  1812. -- Table structure for t_customized
  1813. -- ----------------------------
  1814. DROP TABLE IF EXISTS `t_customized`;
  1815. CREATE TABLE `t_customized` (
  1816. `customized_id` int(11) NOT NULL AUTO_INCREMENT,
  1817. `customized_name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  1818. `customized_shortname` varchar(255) COLLATE utf8_bin DEFAULT '',
  1819. `customized_title` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  1820. `customized_class` varchar(255) COLLATE utf8_bin DEFAULT '',
  1821. `customized_content` varchar(255) COLLATE utf8_bin DEFAULT '',
  1822. `customized_type` tinyint(1) DEFAULT NULL,
  1823. `permission` tinyint(1) DEFAULT 0,
  1824. `user_ids` text COLLATE utf8_bin DEFAULT NULL,
  1825. `ctime` datetime DEFAULT NULL,
  1826. `utime` datetime DEFAULT NULL,
  1827. `company_id` int(11) DEFAULT NULL,
  1828. `customized_other` text COLLATE utf8_bin DEFAULT NULL,
  1829. `view_permission` tinyint(1) NULL DEFAULT 2,
  1830. `view_user_ids` text CHARACTER SET utf8 COLLATE utf8_bin NULL,
  1831. PRIMARY KEY (`customized_id`)
  1832. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  1833. -- ----------------------------
  1834. -- Table structure for t_fast_input
  1835. -- ----------------------------
  1836. DROP TABLE IF EXISTS `t_fast_input`;
  1837. CREATE TABLE `t_fast_input` (
  1838. `fast_input_id` int(11) NOT NULL AUTO_INCREMENT,
  1839. `customized_id` int(11) NOT NULL,
  1840. `fast_input_number` varchar(50) COLLATE utf8_bin NOT NULL DEFAULT '',
  1841. `fast_input_state` tinyint(1) DEFAULT 0,
  1842. `fast_input_data_ids` text COLLATE utf8_bin NOT NULL,
  1843. `fast_input_content` text COLLATE utf8_bin DEFAULT NULL,
  1844. `ctime` datetime DEFAULT NULL,
  1845. `utime` datetime DEFAULT NULL,
  1846. `company_id` int(11) DEFAULT NULL,
  1847. `user_id` int(11) DEFAULT NULL,
  1848. `status` int(2) NULL DEFAULT 99,
  1849. PRIMARY KEY (`fast_input_id`)
  1850. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  1851. -- ----------------------------
  1852. -- Table structure for t_inventory_price_record
  1853. -- ----------------------------
  1854. DROP TABLE IF EXISTS `t_inventory_price_record`;
  1855. CREATE TABLE `t_inventory_price_record` (
  1856. `inventory_price_record_id` int(11) NOT NULL AUTO_INCREMENT,
  1857. `inventory_id` int(11) NOT NULL,
  1858. `record_time` datetime NOT NULL,
  1859. `inventory_price` double(22,2) NOT NULL,
  1860. `notes` text DEFAULT NULL,
  1861. `status` int(2) DEFAULT 99,
  1862. `ctime` datetime DEFAULT NULL,
  1863. `utime` datetime DEFAULT NULL,
  1864. `user_id` int(11) DEFAULT NULL,
  1865. PRIMARY KEY (`inventory_price_record_id`),
  1866. KEY `inventory_id` (`inventory_id`),
  1867. CONSTRAINT `t_inventory_price_record_ibfk_1` FOREIGN KEY (`inventory_id`) REFERENCES `t_inventory` (`inventory_id`) ON DELETE CASCADE ON UPDATE CASCADE
  1868. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1869. -- ----------------------------
  1870. -- Table structure for t_machine_pause
  1871. -- ----------------------------
  1872. DROP TABLE IF EXISTS `t_machine_pause`;
  1873. CREATE TABLE `t_machine_pause` (
  1874. `machine_id` int(11) NOT NULL,
  1875. `ctime` datetime DEFAULT NULL,
  1876. `utime` datetime DEFAULT NULL,
  1877. `user_id` int(11) DEFAULT NULL,
  1878. `permission` tinyint(1) DEFAULT 1,
  1879. `pause_user_ids` text DEFAULT NULL,
  1880. PRIMARY KEY (`machine_id`) USING BTREE
  1881. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  1882. -- ----------------------------
  1883. -- Table structure for t_machine_stop_reason
  1884. -- ----------------------------
  1885. DROP TABLE IF EXISTS `t_machine_stop_reason`;
  1886. CREATE TABLE `t_machine_stop_reason` (
  1887. `stop_reason_id` int(11) NOT NULL AUTO_INCREMENT,
  1888. `stop_reason_name` varchar(255) NOT NULL,
  1889. `ctime` datetime DEFAULT NULL,
  1890. `utime` datetime DEFAULT NULL,
  1891. `user_id` int(11) DEFAULT NULL,
  1892. `permission` tinyint(1) DEFAULT 1,
  1893. `stop_reason_user_ids` text DEFAULT NULL,
  1894. PRIMARY KEY (`stop_reason_id`) USING BTREE
  1895. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  1896. -- ----------------------------
  1897. -- Table structure for t_offer_cost_float
  1898. -- ----------------------------
  1899. DROP TABLE IF EXISTS `t_offer_cost_float`;
  1900. CREATE TABLE `t_offer_cost_float` (
  1901. `offer_cost_float_id` int(11) NOT NULL AUTO_INCREMENT,
  1902. `offer_cost_float_number` double(13,2) DEFAULT 0.00,
  1903. `product_ids` text DEFAULT NULL,
  1904. `ctime` datetime DEFAULT NULL,
  1905. `utime` datetime DEFAULT NULL,
  1906. `user_id` int(11) DEFAULT NULL,
  1907. PRIMARY KEY (`offer_cost_float_id`) USING BTREE
  1908. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  1909. -- ----------------------------
  1910. -- Table structure for t_offer_price
  1911. -- ----------------------------
  1912. DROP TABLE IF EXISTS `t_offer_price`;
  1913. CREATE TABLE `t_offer_price` (
  1914. `offer_price_id` int(11) NOT NULL AUTO_INCREMENT,
  1915. `offer_price_des` varchar(255) NOT NULL,
  1916. `offer_price_number` double(13,2) DEFAULT 0.00,
  1917. `ctime` datetime DEFAULT NULL,
  1918. `utime` datetime DEFAULT NULL,
  1919. `user_id` int(11) DEFAULT NULL,
  1920. `permission` tinyint(1) DEFAULT 1,
  1921. `see_user_ids` text DEFAULT NULL,
  1922. PRIMARY KEY (`offer_price_id`) USING BTREE
  1923. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  1924. -- ----------------------------
  1925. -- Table structure for t_user_report_password
  1926. -- ----------------------------
  1927. DROP TABLE IF EXISTS `t_user_report_password`;
  1928. CREATE TABLE `t_user_report_password` (
  1929. `user_id` int(11) NOT NULL,
  1930. `report_password` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  1931. `report_password_type` tinyint(1) NOT NULL DEFAULT 0,
  1932. `report_password_interval` int(11) DEFAULT 0,
  1933. `use_time` datetime DEFAULT NULL,
  1934. `ctime` datetime DEFAULT NULL,
  1935. PRIMARY KEY (`user_id`) USING BTREE,
  1936. CONSTRAINT `t_user_report_password_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
  1937. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  1938. -- ----------------------------
  1939. -- Table structure for t_user_report_password
  1940. -- ----------------------------
  1941. DROP TABLE IF EXISTS `t_product_watermark`;
  1942. CREATE TABLE `t_product_watermark` (
  1943. `watermark_id` int(11) NOT NULL AUTO_INCREMENT,
  1944. `product_id` int(11) NOT NULL,
  1945. `user_id` int(11) DEFAULT NULL,
  1946. `company_id` int(11) DEFAULT NULL,
  1947. `ctime` datetime DEFAULT NULL,
  1948. `utime` datetime DEFAULT NULL,
  1949. `watermark_name` varchar(50) COLLATE utf8_bin DEFAULT '',
  1950. `logo_image_id` int(11) DEFAULT NULL,
  1951. `watermark_title` varchar(50) COLLATE utf8_bin DEFAULT '',
  1952. `font_type` tinyint(1) DEFAULT 0,
  1953. `watermark_colour` varchar(50) COLLATE utf8_bin DEFAULT '',
  1954. `transparency` int(11) DEFAULT 0,
  1955. `font_szie` int(11) DEFAULT 0,
  1956. `row_number` int(11) DEFAULT 0,
  1957. `row_spacing` int(11) DEFAULT 0,
  1958. `column_number` int(11) DEFAULT 0,
  1959. `column_spacing` int(11) DEFAULT 0,
  1960. `angle` int(11) DEFAULT 0,
  1961. PRIMARY KEY (`watermark_id`,`product_id`),
  1962. UNIQUE KEY `watermark_id` (`watermark_id`) USING BTREE,
  1963. UNIQUE KEY `product_id` (`product_id`) USING BTREE
  1964. ) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC;
  1965. -- ----------------------------
  1966. -- Table structure for t_tag_group
  1967. -- ----------------------------
  1968. DROP TABLE IF EXISTS `t_tag_group`;
  1969. CREATE TABLE `t_tag_group` (
  1970. `tag_group_id` int(11) NOT NULL AUTO_INCREMENT,
  1971. `tag_group_title` varchar(255) NOT NULL,
  1972. `tag_group_notes` text DEFAULT NULL,
  1973. `tag_category` tinyint(1) DEFAULT NULL,
  1974. `status` tinyint(1) DEFAULT 99,
  1975. `ctime` datetime DEFAULT NULL,
  1976. `utime` datetime DEFAULT NULL,
  1977. `dtime` datetime DEFAULT NULL,
  1978. PRIMARY KEY (`tag_group_id`) USING BTREE
  1979. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  1980. -- ----------------------------
  1981. -- Function structure for Fun_GetPY
  1982. -- ----------------------------
  1983. SET GLOBAL log_bin_trust_function_creators = 1;
  1984. delimiter ;;
  1985. CREATE FUNCTION IF NOT EXISTS `Fun_GetPY`(in_string VARCHAR(255) CHARSET utf8)
  1986. RETURNS varchar(255) CHARSET utf8
  1987. BEGIN
  1988. #截取字符串,每次做截取后的字符串存放在该变量中,初始为函数参数in_string值
  1989. DECLARE tmp_str VARCHAR(255) CHARSET gbk DEFAULT '' ;
  1990. #tmp_str的长度
  1991. DECLARE tmp_len SMALLINT DEFAULT 0;
  1992. #tmp_str的长度
  1993. DECLARE tmp_loc SMALLINT DEFAULT 0;
  1994. #截取字符,每次 left(tmp_str,1) 返回值存放在该变量中
  1995. DECLARE tmp_char VARCHAR(2) CHARSET gbk DEFAULT '';
  1996. #结果字符串
  1997. DECLARE tmp_rs VARCHAR(255)CHARSET gbk DEFAULT '';
  1998. #拼音字符,存放单个汉字对应的拼音首字符
  1999. DECLARE tmp_cc VARCHAR(2) CHARSET gbk DEFAULT '';
  2000. #初始化,将in_string赋给tmp_str
  2001. SET tmp_str = in_string;
  2002. #初始化长度
  2003. SET tmp_len = LENGTH(tmp_str);
  2004. #如果被计算的tmp_str长度大于0则进入该while
  2005. WHILE tmp_len > 0 DO
  2006. #获取tmp_str最左端的首个字符,注意这里是获取首个字符,该字符可能是汉字,也可能不是。
  2007. SET tmp_char = LEFT(tmp_str,1);
  2008. #左端首个字符赋值给拼音字符
  2009. SET tmp_cc = tmp_char;
  2010. #获取字符的编码范围的位置,为了确认汉字拼音首字母是那一个
  2011. SET tmp_loc=INTERVAL(CONV(HEX(tmp_char),16,10),0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC
  2012. ,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA ,0xCEF4,0xD1B9,0xD4D1);
  2013. #判断左端首个字符是多字节还是单字节字符,要是多字节则认为是汉字且作以下拼音获取,要是单字节则不处理。如果是多字节字符但是不在对应的编码范围之内,即对应的不是大写字母则也不做处理,这样数字或者特殊字符就保持原样了
  2014. IF (LENGTH(tmp_char)>1 AND tmp_loc>0 AND tmp_loc<24) THEN
  2015. #获得汉字拼音首字符
  2016. SELECT ELT(tmp_loc,'a','b','c','d','e','f','g','h','j','k','l','m','n','o','p','q','r','s','t','w','x','y','z') INTO tmp_cc;
  2017. END IF;
  2018. IF ASCII(tmp_cc)>96 AND ASCII(tmp_cc) <123
  2019. THEN
  2020. #将当前tmp_str左端首个字符拼音首字符与返回字符串拼接
  2021. SET tmp_rs = CONCAT(tmp_rs,tmp_cc);
  2022. ELSE
  2023. SET tmp_rs = CONCAT(tmp_rs,tmp_char);
  2024. END IF;
  2025. #将tmp_str左端首字符去除
  2026. SET tmp_str = SUBSTRING(tmp_str,2);
  2027. #计算当前字符串长度
  2028. SET tmp_len = LENGTH(tmp_str);
  2029. END WHILE;
  2030. #返回结果字符串
  2031. RETURN tmp_rs;
  2032. END
  2033. ;;
  2034. delimiter ;
  2035. SET FOREIGN_KEY_CHECKS = 1;