From 2f58ddd5085b9e51bcc4b7e1ad2e850a95e43bbd Mon Sep 17 00:00:00 2001 From: gongchunyi <deslre0381@gmail.com> Date: 星期三, 11 三月 2026 17:42:59 +0800 Subject: [PATCH] feat: 同步物料编码数据 --- doc/宁夏-中盛建材.sql | 185 ++++++++++++++++++++++++++++++++++----------- 1 files changed, 138 insertions(+), 47 deletions(-) diff --git "a/doc/\345\256\201\345\244\217-\344\270\255\347\233\233\345\273\272\346\235\220.sql" "b/doc/\345\256\201\345\244\217-\344\270\255\347\233\233\345\273\272\346\235\220.sql" index 16572ba..7c58d7a 100644 --- "a/doc/\345\256\201\345\244\217-\344\270\255\347\233\233\345\273\272\346\235\220.sql" +++ "b/doc/\345\256\201\345\244\217-\344\270\255\347\233\233\345\273\272\346\235\220.sql" @@ -1,53 +1,144 @@ -CREATE TABLE `prod_demand_main` +DROP TABLE IF EXISTS `production_plan`; + +CREATE TABLE production_plan ( - `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '鑷涓婚敭', - `tenant_id` bigint(20) NOT NULL COMMENT '绉熸埛缂栧彿', - `dept_id` bigint(20) DEFAULT NULL COMMENT '閮ㄩ棬ID (鐢ㄤ簬鏁版嵁鏉冮檺闅旂)', - `form_instance_id` varchar(64) NOT NULL COMMENT '琛ㄥ崟瀹炰緥鍞竴ID (鍏宠仈瀛愯〃鐢�)', - `serial_no` varchar(64) DEFAULT NULL COMMENT '鍗曟嵁娴佹按鍙� (serialNo)', - `title` varchar(128) DEFAULT NULL COMMENT '鍗曟嵁鏍囬 (title)', - `originator_user_id` varchar(64) DEFAULT NULL COMMENT '绗笁鏂瑰彂璧蜂汉ID (creatorUserId)', - `originator_name` varchar(64) DEFAULT NULL COMMENT '绗笁鏂瑰彂璧蜂汉濮撳悕 (originator.userName)', - `biz_create_time` datetime DEFAULT NULL COMMENT '绗笁鏂瑰崟鎹垱寤烘椂闂� (createdTimeGMT)', - `project_name` varchar(128) DEFAULT NULL COMMENT '椤圭洰鍚嶇О/瀹㈡埛 (textField_lbkozohg)', - `batch_no` varchar(128) DEFAULT NULL COMMENT '鎵规/鍏宠仈缂栧彿 (textField_l7fytfco)', - `contact_person` varchar(128) DEFAULT NULL COMMENT '鑱旂郴浜�/澶囨敞 (textareaField_l7fytfcy)', - `ext_text1` varchar(128) DEFAULT NULL COMMENT '棰勭暀鏂囨湰1 (textField_l7fytfcx)', - `ext_text2` varchar(128) DEFAULT NULL COMMENT '棰勭暀鏂囨湰2 (textField_lbknlltp)', - `create_user` bigint(20) DEFAULT NULL COMMENT '鍒涘缓鑰�', - `create_time` datetime DEFAULT NULL COMMENT '鍒涘缓鏃堕棿', - `update_user` bigint(20) DEFAULT NULL COMMENT '鏇存柊鑰�', - `update_time` datetime DEFAULT NULL COMMENT '鏇存柊鏃堕棿', - `remark` varchar(500) DEFAULT NULL COMMENT '澶囨敞', + id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '涓婚敭', + form_instance_id VARCHAR(64) COMMENT '琛ㄥ崟瀹炰緥ID', + serial_no VARCHAR(50) COMMENT '娴佹按鍙�', + apply_no VARCHAR(100) COMMENT '鐢宠鍗曠紪鍙�', + customer_name VARCHAR(200) COMMENT '瀹㈡埛鍚嶇О', + material_code VARCHAR(100) COMMENT '鐗╂枡缂栫爜', + product_name VARCHAR(200) COMMENT '浜у搧鍚嶇О', + product_spec VARCHAR(200) COMMENT '浜у搧瑙勬牸', + length INT COMMENT '闀�', + width INT COMMENT '瀹�', + height INT COMMENT '楂�', + quantity INT COMMENT '鍧楁暟', + total_count INT COMMENT '姝ゆ缁熻鏁伴噺', + volume DECIMAL(10, 4) COMMENT '鏂规暟', + strength VARCHAR(50) COMMENT '寮哄害', + start_date DATETIME COMMENT '寮�濮嬫棩鏈�', + end_date DATETIME COMMENT '缁撴潫鏃ユ湡', + submitter VARCHAR(100) COMMENT '鎻愪氦浜�', + submit_org VARCHAR(200) COMMENT '鎻愪氦浜虹粍缁�', + remark_one TEXT COMMENT '澶囨敞1', + remark_two TEXT COMMENT '澶囨敞2', + creator_name VARCHAR(100) COMMENT '鍒涘缓浜�', + modifier_name VARCHAR(100) COMMENT '淇敼浜�', + form_created_time DATETIME COMMENT '琛ㄥ崟鍒涘缓鏃堕棿', + form_modified_time DATETIME COMMENT '琛ㄥ崟淇敼鏃堕棿', + data_sync_type TINYINT DEFAULT 1 COMMENT '鏁版嵁鍚屾绫诲瀷锛�1=鎵嬪姩 2=瀹氭椂浠诲姟', + data_source_type TINYINT DEFAULT 1 COMMENT '鏁版嵁鏉ユ簮绫诲瀷锛�1=閿�鍞鍗� 2=閿�鍞娴�', + create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '鏁版嵁搴撳垱寤烘椂闂�', + update_time DATETIME COMMENT '鏁版嵁搴撴洿鏂版椂闂�', - PRIMARY KEY (`id`), - UNIQUE KEY `uk_form_instance_id` (`form_instance_id`) + INDEX idx_apply_no (apply_no), + INDEX idx_serial_no (serial_no), + INDEX idx_form_instance_id (form_instance_id), + INDEX idx_material_code (material_code) ) ENGINE = InnoDB - DEFAULT CHARSET = utf8mb4 COMMENT ='閿�鍞敓浜ч渶姹�'; + DEFAULT CHARSET = utf8mb4 COMMENT ='閿�鍞敓浜ц鍒�'; -CREATE TABLE `prod_demand_detail` +CREATE TABLE `product-inventory-management-zsjc`.`energy` ( + `id` int NOT NULL AUTO_INCREMENT, + `energy_tyep` varchar(255) NULL COMMENT '鑳芥簮绫诲瀷', + `energy_name` varchar(255) NULL COMMENT '鑳芥簮鍚嶇О', + `unit` varchar(255) NULL COMMENT '鍗曚綅', + `unit_price` decimal(10, 2) NULL COMMENT '鍗曚环', + `remark` varchar(255) NULL COMMENT '澶囨敞', + `create_time` datetime(0) NULL DEFAULT NULL COMMENT '鍒涘缓鏃堕棿', + `update_time` datetime(0) NULL DEFAULT NULL COMMENT '鏇存柊鏃堕棿', + `create_user` bigint NULL DEFAULT NULL COMMENT '鍒涘缓鑰�', + `update_user` bigint NULL DEFAULT NULL COMMENT '鏇存柊鑰�', + PRIMARY KEY (`id`) + ) COMMENT = '鑳芥簮绫诲瀷'; + +CREATE TABLE `product-inventory-management-zsjc`.`energy_consumption_detail` ( + `id` int NOT NULL AUTO_INCREMENT, + `energy_id` int NULL COMMENT '澶栨鍏宠仈鑳芥簮绫诲瀷id', + `type` varchar(255) NULL COMMENT '鍔炲叕/鐢熶骇', + `meter_reading_location` varchar(255) NULL COMMENT '鎶勮〃浣嶇疆', + `meter_reading_date` date NULL COMMENT '鎶勮〃鏃ユ湡', + `start_code` decimal(10, 2) NULL COMMENT '璧风爜', + `stop_code` decimal(10, 2) NULL COMMENT '姝㈢爜', + `dosage` decimal(10, 2) NULL COMMENT '鐢ㄩ噺', + `remark` varchar(255) NULL COMMENT '澶囨敞', + `create_time` datetime(0) NULL DEFAULT NULL COMMENT '鍒涘缓鏃堕棿', + `update_time` datetime(0) NULL DEFAULT NULL COMMENT '鏇存柊鏃堕棿', + `create_user` bigint NULL DEFAULT NULL COMMENT '鍒涘缓鑰�', + `update_user` bigint NULL DEFAULT NULL COMMENT '鏇存柊鑰�', + PRIMARY KEY (`id`) + ) COMMENT = '鑳芥簮绫诲瀷-鑳借�楁妱琛ㄦ槑缁�'; + +CREATE TABLE `product-inventory-management-zsjc`.`energy_consumption_detail_file` ( + `id` int NOT NULL AUTO_INCREMENT, + `energy_consumption_detail_id` int NULL COMMENT '鍏宠仈澶栭敭鑳借�楁妱琛╥d', + `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, + `url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, + `file_size` double NULL DEFAULT NULL, + `create_time` timestamp(0) NULL DEFAULT NULL, + `create_user` int NULL DEFAULT NULL, + `update_time` timestamp(0) NULL DEFAULT NULL, + `update_user` int NULL DEFAULT NULL, + PRIMARY KEY (`id`) + ) COMMENT = '鑳芥簮绫诲瀷-鑳借�楁妱琛ㄦ槑缁哶闄勪欢'; + +alter table product_order +drop column sales_ledger_id, +drop column sale_ledger_product_id, +drop column product_model_id; + +alter table production_plan + add assigned_quantity DECIMAL(10, 4) default 0 not null COMMENT '涓嬪彂鏁伴噺'; + +alter table product_order + add plan_complete_time datetime(0) NULL DEFAULT NULL COMMENT '璁″垝瀹屾垚鏃堕棿'; + +# 鐢熶骇璁㈠崟涓庣敓浜ц鍒掑叧鑱旇〃 +drop table if exists product_order_plan; +create table product_order_plan ( - `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '鑷涓婚敭', - `tenant_id` bigint(20) NOT NULL COMMENT '绉熸埛缂栧彿', - `prod_demand_main_id` bigint(20) NOT NULL COMMENT '鐖剁被鍏宠仈ID', - `material_code` varchar(64) DEFAULT NULL COMMENT '鐗╂枡缂栫爜 (textField_l9xo62q5)', - `material_category` varchar(64) DEFAULT NULL COMMENT '鐗╂枡鍒嗙被 (textField_l9xo62q7)', - `spec_desc` varchar(128) DEFAULT NULL COMMENT '瑙勬牸鎻忚堪 (textField_l9xo62q8)', - `grade_type` varchar(32) DEFAULT NULL COMMENT '绛夌骇/绫诲瀷 (radioField_m9urarr2)', - `length` decimal(10, 2) DEFAULT NULL COMMENT '闀垮害 (numberField_lb7lgatg)', - `width` decimal(10, 2) DEFAULT NULL COMMENT '瀹藉害 (numberField_lb7lgath)', - `thickness` decimal(10, 2) DEFAULT NULL COMMENT '鍘氬害 (numberField_lb7lgati)', - `quantity` int(11) DEFAULT NULL COMMENT '鏁伴噺 (numberField_lb7lgatj)', - `volume` decimal(10, 4) DEFAULT NULL COMMENT '鎬讳綋绉� (numberField_l7fytfd3)', - `date_range` varchar(255) DEFAULT NULL COMMENT '鏃堕棿鍖洪棿鏁扮粍瀛楃涓� (cascadeDateField_lfxqqluw)', - `association_receipt` text DEFAULT NULL COMMENT '鍏宠仈鐨勫叾浠栧崟鎹� (associationFormField_l9xo62q6_id)', - `create_user` bigint(20) DEFAULT NULL COMMENT '鍒涘缓鑰�', - `create_time` datetime DEFAULT NULL COMMENT '鍒涘缓鏃堕棿', - `update_user` bigint(20) DEFAULT NULL COMMENT '鏇存柊鑰�', - `update_time` datetime DEFAULT NULL COMMENT '鏇存柊鏃堕棿', - `remark` varchar(500) DEFAULT NULL COMMENT '澶囨敞', + id bigint auto_increment primary key, + product_order_id bigint not null default 0 comment '鐢熶骇璁㈠崟id', + production_plan_id bigint not null default 0 comment '鐢熶骇璁″垝id', + create_time datetime null comment '褰曞叆鏃堕棿', + update_time datetime null comment '鏇存柊鏃堕棿', + index idx_product_order_id (product_order_id), + index idx_production_plan_id (production_plan_id), + unique idx_product_order_production_plan (product_order_id, production_plan_id) +); - PRIMARY KEY (`id`), - KEY `idx_prod_demand_main_id` (`prod_demand_main_id`) -) ENGINE = InnoDB - DEFAULT CHARSET = utf8mb4 COMMENT ='閿�鍞敓浜ч渶姹俖鏄庣粏琛�'; \ No newline at end of file +CREATE TABLE `product_material` +( + `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '涓婚敭ID', + `tenant_id` BIGINT DEFAULT NULL COMMENT '绉熸埛ID', + `material_type_id` INT DEFAULT NULL COMMENT '鍏宠仈鐗╂枡绫诲瀷ID', + `inventory_category_id` INT DEFAULT NULL COMMENT '鍏宠仈瀛樿揣绫诲埆ID', + `identifier_code` VARCHAR(100) DEFAULT NULL COMMENT '鏍囪瘑缂栫爜', + `material_code` VARCHAR(100) DEFAULT NULL COMMENT '鐗╂枡浠ｇ爜', + `product_name` VARCHAR(255) DEFAULT NULL COMMENT '浜у搧鍚嶇О', + `material_name` VARCHAR(255) DEFAULT NULL COMMENT '鐗╂枡鍝佸悕', + `specification` VARCHAR(255) DEFAULT NULL COMMENT '瑙勬牸鍨嬪彿', + `base_unit` VARCHAR(50) DEFAULT NULL COMMENT '鍩烘湰鍗曚綅', + `material_attribute` VARCHAR(100) DEFAULT NULL COMMENT '鐗╂枡灞炴��', + `finished_product_name` VARCHAR(100) DEFAULT NULL COMMENT '鎴愬搧鍝佸悕', + `originator_name` VARCHAR(100) DEFAULT NULL COMMENT '鎻愪氦浜哄鍚�', + `originator_org` VARCHAR(255) DEFAULT '瀹佸涓垱缁胯兘瀹炰笟闆嗗洟鏈夐檺鍏徃', + `remark` TEXT COMMENT '澶囨敞', + `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP, + `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + INDEX `idx_type_id` (`material_type_id`), + INDEX `idx_cat_id` (`inventory_category_id`) +) ENGINE = INNODB + DEFAULT CHARSET = utf8mb4 COMMENT = '鐗╂枡淇℃伅琛�'; + +ALTER TABLE product_material + ADD COLUMN form_instance_id VARCHAR(100) DEFAULT NULL COMMENT '瀹滄惌琛ㄥ崟瀹炰緥ID', + ADD COLUMN form_modified_time DATETIME DEFAULT NULL COMMENT '瀹滄惌淇敼鏃堕棿'; + +CREATE TABLE `product_material_config` +( + `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY, + `config_type` varchar(50) NOT NULL COMMENT '鍖哄垎绫诲瀷: MATERIAL_TYPE 鎴� INVENTORY_CAT', + `config_name` varchar(100) NOT NULL COMMENT '鏄剧ず鐨勫悕绉�' +) ENGINE = InnoDB COMMENT ='鐗╂枡淇℃伅琛ㄩ厤缃〃'; \ No newline at end of file -- Gitblit v1.9.3