订单系统阶段上线备忘

CREATE TABLE `pm_order_payment` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '编号',
  `order_id` varchar(50) NOT NULL DEFAULT '' COMMENT '订单号',
  `caiwu_payment_id` varchar(50) NOT NULL DEFAULT '' COMMENT '财务系统回款记录id',
  `caiwu_price` varchar(10) NOT NULL DEFAULT '0' COMMENT '财务系统回款金额',
  `caiwu_date` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '财务系统回款日期',
  `caiwu_createtime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '财务系统创建时间',
  `caiwu_status` varchar(10) NOT NULL DEFAULT '1' COMMENT '财务系统回款状态,1正常,2撤销',
  `create_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_order_id` (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='回款记录表-lyj';

ALTER TABLE `digital_test`.`pm_order_contract` 
ADD COLUMN `sign_customer_type` smallint(5) UNSIGNED NOT NULL DEFAULT 0 COMMENT '签约分类' AFTER `invoice_rate_i18n`,
ADD COLUMN `sign_customer_type_i18n` varchar(255) NOT NULL DEFAULT '' COMMENT '签约分类' AFTER `sign_custmoer_type`;
ALTER TABLE `digital_test`.`pm_order_contract` 
ADD COLUMN `project_source` smallint(5) UNSIGNED NOT NULL DEFAULT 0 COMMENT '项目来源' AFTER `sign_customer_type_i18n`,
ADD COLUMN `project_source_i18n` varchar(255) NOT NULL DEFAULT '' COMMENT '项目来源' AFTER `project_source`;
ALTER TABLE `digital_test`.`pm_order_contract` 
ADD COLUMN `is_fate_lead` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否提前开票' AFTER `is_overseas`;
ALTER TABLE `digital_test`.`pm_order_contract` 
ADD COLUMN `payment_remark` varchar(500) NOT NULL DEFAULT '' AFTER `payment_method`;
ALTER TABLE `digital_test`.`pm_order_contract` 
ADD COLUMN `project_source_type` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 AFTER `project_source_i18n`,
ADD COLUMN `project_source_remark` varchar(255) NOT NULL DEFAULT '' AFTER `project_source_type`;

ALTER TABLE `digital_test`.`pm_order_docs` 
MODIFY COLUMN `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '备注信息' AFTER `attachment`,
ADD COLUMN `docs_text` varchar(255) NOT NULL COMMENT '验收的其他描述信息' AFTER `remark`;

ALTER TABLE `pm_order_info` 
ADD COLUMN `product_is_oem` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否Oem' AFTER `product_is_standard`,
ADD COLUMN `product_is_overseas` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否海外' AFTER `product_is_oem`;

ALTER TABLE `digital_test`.`pm_product_list` 
ADD COLUMN `is_oem` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否Oem' AFTER `is_standard`,
ADD COLUMN `is_overseas` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否海外' AFTER `is_oem`;

ALTER TABLE `digital_test`.`pm_order` 
ADD COLUMN `order_type` tinyint(3) UNSIGNED NOT NULL DEFAULT 1 COMMENT '订单类型 1正式  2试做' AFTER `order_id`;

ALTER TABLE `digital_test`.`pm_order_trytodo` 
ADD COLUMN `docs` text NOT NULL COMMENT '试做验收交付物上传内容' AFTER `status`,
ADD COLUMN `docs_userid` varchar(50) NULL DEFAULT '' COMMENT '交付物上传Id' AFTER `docs`,
ADD COLUMN `docs_username` varchar(255) NULL DEFAULT '' COMMENT '交付物上传人' AFTER `docs_userid`,
ADD COLUMN `docs_time` datetime(0) NULL DEFAULT '2000-01-01 00:00:00' COMMENT '交付物上传时间' AFTER `docs_username`;

ALTER TABLE `digital_test`.`pm_order_trytodo` 
MODIFY COLUMN `docs` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '试做验收交付物上传内容' AFTER `status`,
MODIFY COLUMN `docs_userid` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '交付物上传Id' AFTER `docs`,
MODIFY COLUMN `docs_username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '交付物上传人' AFTER `docs_userid`,
MODIFY COLUMN `docs_time` datetime(0) NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '交付物上传时间' AFTER `docs_username`;

ALTER TABLE `digital_test`.`pm_order_contract` 
ADD COLUMN `trytodo_order_json` mediumtext NULL COMMENT '关联试做订单' AFTER `history_order_json`;

ALTER TABLE `digital_test`.`pm_order_contract` 
ADD COLUMN `sign_company` varchar(50) NOT NULL DEFAULT '' COMMENT '签约公司' AFTER `seal_type_i18n`,
ADD COLUMN `sign_company_i18n` varchar(255) NOT NULL DEFAULT '' COMMENT '签约公司主体' AFTER `sign_company`;

UPDATE pm_processl_instance SET title=approval_id WHERE approval_category="pm";

DROP VIEW pm_order_search_index;
CREATE VIEW pm_order_search_index AS SELECT
title AS order_id,
create_userid,
group_concat( approval_now_userid_list ) AS approval_now_userid_list,
group_concat( approval_done_userid_list ) AS approval_done_userid_list,
group_concat( handler_now_userid_list ) AS handler_now_userid_list,
group_concat( handler_done_userid_list ) AS handler_done_userid_list,
group_concat( cc_userid_list ) AS cc_userid_list 
FROM
	pm_processl_instance 
WHERE
	approval_category = 'pm' 
GROUP BY
	title;

ALTER TABLE `digital_test`.`pm_order` 
MODIFY COLUMN `process` enum('order_create','order_approval','order_excute','order_trytodo','order_contract') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'order_create' COMMENT '流程环节' AFTER `assess_price`;

ALTER TABLE `digital_test`.`pm_order_process_track` 
ADD COLUMN `item_id` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单号，子试做任务的ID' AFTER `order_id`;

注意！！！！！！！！ ： item_id要更新为varchar 50